mysql復(fù)制主從集群搭建
來源：程序員人生 發(fā)布時(shí)間：2014-09-27 02:28:46 閱讀次數(shù)：3482次
最近搭了個(gè)主從復(fù)制，中間出了點(diǎn)小問題，排查搞定，記錄下來
1
環(huán)境：
虛擬機(jī)：
OS:
centos6.5
Linux host2 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
服務(wù)器IP
192.168.18.66
192.168.18.67
DB：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.20 |
+-----------+
2
主機(jī)：192.168.18.66
從機(jī)：192.168.18.67
3
修改主服務(wù)器配置,添加如下內(nèi)容：
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest
此時(shí)主服務(wù)器這個(gè)配置文件/etc/my.cnf內(nèi)容如下：
[client]
#password = system
#port = 3306
default-character-set=utf8
[mysqld]
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500
[mysql]
default-character-set=utf8
4
修改從服務(wù)器配置
server-id=20
relay_log=mysql-relay-bin
read_only
此時(shí)從服務(wù)器配置文件內(nèi)容如下：
[client]
#password=system
#port=3306
default-character-set=utf8
[mysqld]
server-id=20
relay_log=mysql-relay-bin
#read_only
#log_slave_updates=1
#master-host=192.168.18.66
#master-user=repl
#master-password=123
#master-port=3306
#master-connect-retry=60
#replicate_do_db=reptest
#replicate_ignore_db=mysql,information_schema,performance_schema
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500
[mysql]
default-character-set=utf8
mysql復(fù)制的相關(guān)參數(shù)可參考下面的網(wǎng)頁：
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
5
在主上增加復(fù)制用戶
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;
192.168.18.67是從服務(wù)器，就通過repl用戶密碼為空來同步復(fù)制
mysql> select host,user,Repl_slave_priv from mysql.user where user='repl';
+---------------+------+-----------------+
| host | user | Repl_slave_priv |
+---------------+------+-----------------+
| 192.168.18.67 | repl | Y |
+---------------+------+-----------------+
1 row in set (0.00 sec)
6
重啟主從服務(wù)器：
停主，停從
mysqladmin -uroot shutdown -psystem
起從，起主
/etc/init.d/mysql start
[root@host3 ~]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!
7
導(dǎo)出主數(shù)據(jù)庫數(shù)據(jù)，取快照
1）鎖主庫
flush tables with read lock;
2)
這一步比較重要，要記住File和Position值，在起從服務(wù)器上的slave線程時(shí)備用
mysql> show master status G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 401
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
3)
[root@host3 ~]# mysqldump -uroot -p reptest --triggers --routines --events > /home/zxw/master_reptest.sql
順便看一下，mysqldump的內(nèi)容如下：
[root@host3 ~]# ll /home/zxw/
total 4
-rw-r--r--. 1 root root 1910 Aug 25 13:50 master_reptest.sql
[root@host3 ~]# nl /home/zxw/master_reptest.sql
1 -- MySQL dump 10.13 Distrib 5.6.20, for Linux (x86_64)
2 --
3 -- Host: localhost Database: reptest
4 -- ------------------------------------------------------
5 -- Server version 5.6.20-log
6 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
7 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
8 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
9 /*!40101 SET NAMES utf8 */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40103 SET TIME_ZONE='+00:00' */;
12 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
16 --
17 -- Table structure for table `tbldata`
18 --
19 DROP TABLE IF EXISTS `tbldata`;
20 /*!40101 SET @saved_cs_client = @@character_set_client */;
21 /*!40101 SET character_set_client = utf8 */;
22 CREATE TABLE `tbldata` (
23 `id` int(11) DEFAULT NULL
24 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
25 /*!40101 SET character_set_client = @saved_cs_client */;
26 --
27 -- Dumping data for table `tbldata`
28 --
29 LOCK TABLES `tbldata` WRITE;
30 /*!40000 ALTER TABLE `tbldata` DISABLE KEYS */;
31 INSERT INTO `tbldata` VALUES (1),(2),(3);
32 /*!40000 ALTER TABLE `tbldata` ENABLE KEYS */;
33 UNLOCK TABLES;
34 --
35 -- Dumping events for database 'reptest'
36 --
37 --
38 -- Dumping routines for database 'reptest'
39 --
40 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
41 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
42 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
43 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
44 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
45 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
46 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
47 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
48 -- Dump completed on 2014-08-25 13:50:48
4)
解鎖數(shù)據(jù)庫
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
############################
###拷貝數(shù)據(jù)文件目錄方式#####
############################
#第二種取主數(shù)據(jù)庫快照的方法
#mysqladmin -uroot shutdown
#打包數(shù)據(jù)庫數(shù)據(jù)目錄，例如數(shù)據(jù)目錄是/data/dbdata：
#cd /data
#tar zcvf dbdata.tar.gz dbdata
#備份后就可以啟動(dòng)主服務(wù)器了：
#mysqld_safe
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
