閱讀172 返回首頁    go 微軟 go windows

Centos7下配置安裝mysql5.5主從複製（一主兩從）

1：安裝mysql5.5數據庫

規劃：

主：server-1—10.64.5.167

從：server-2—10.64.5.170

從：server-3—10.64.5.172

數據目錄：/var/lib/mysql

下載mysql5.5

wget https://cdn.mysql.com//Downloads/MySQL-5.5/MySQL-client-5.5.46-1.el7.x86_64.rpm

wget https://cdn.mysql.com//Downloads/MySQL-5.5/MySQL-server-5.5.46-1.el7.x86_64.rpm

安裝

rpm -ivh MySQL-server-5.5.46-1.el7.x86_64.rpm

rpm -ivh MySQL-client-5.5.46-1.el7.x86_64.rpm

拷貝配置文件

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

自啟動：

systemctl enable mysqld

2：配置mysql主庫

主：server-1 10.64.5.167

（1）配置創建需要同步的數據庫cattle。

mysql

mysql>CREATEDATABASE IF NOT EXISTS cattle COLLATE='utf8_general_ci' CHARACTER SET='utf8';
mysql>GRANT ALL ON cattle.*TO 'cattle'@'%' IDENTIFIED BY 'cattle';
mysql>GRANT ALL ON cattle.*TO 'cattle'@'localhost' IDENTIFIED BY 'cattle';
（2）創建用戶

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON . TO slave1@'10.64.5.170' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON . TO slave2@'10.64.5.172' IDENTIFIED BY '123456';
mysql> flush privileges;
（3）配置文件

vim /etc/my.cnf

添加
server-id = 1
log-bin=mysql-bin
log-slave-updatesbinlog-do-db=cattle
binlog-ignore-db=mysql
重啟mysql

（4）鎖主庫表

mysql> FLUSH TABLES WITH READ LOCK;
（4）顯示主庫信息

mysql> SHOW MASTER STATUS;
（5）另開一個終端，打包主庫

cd /var/lib/mysql

tar czvf cattle.tar.gz cattle

（6）解鎖主庫表

mysql> UNLOCK TABLES;
3：配置mysql從庫

從：server-2 10.64.5.170

從：server-3 10.64.5.172

（1）將cattle.tar.gz 傳輸到slav機器

mv cattle.tar.gz /var/lib/mysql/

cd /var/lib/mysql/

tar xf cattle.tar.gz

（2）查看修改cattle文件夾權限

chown -R mysql:mysql cattle

（3）配置文件

vim /etc/my.cnf

------------server-2 添加
server-id =2
log_bin = mysql-bin
relay_log = mysql-relay-bin
read-only=1
replicate-do-db=cattle
log-slave-updates=1
------------server-3 添加
server-id =3
log_bin = mysql-bin
relay_log = mysql-relay-bin
read-only=1
replicate-do-db=cattle
log-slave-updates=1
重啟slave的mysql

（4）驗證連接

從庫server-2上測試連接主庫

mysql -h10.64.5.167 -uslave1 -p123456

mysql> show grants for slave1@10.64.5.170；
從庫server-3上測試連接主庫

mysql -h10.64.5.167 -uslave2 -p123456

mysql> show grants for slave2@10.64.5.172；
（5）設置slave複製

查詢master的position值

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB: cattle
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
ERROR:
No query specified
server-2配置
mysql>CHANGE MASTER TO MASTER_HOST='10.64.5.167',
-> MASTER_USER='slave1',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=120; -------MASTER_LOG_POS為主庫的Position
server-3配置
mysql>CHANGE MASTER TO MASTER_HOST='10.64.5.167',
-> MASTER_USER='slave2',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=120;
（6）slave啟動

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
運行SHOW SLAVE STATUS查看輸出結果：

主要查看

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.5.167
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 178995708
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: cattle
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 178995562
Relay_Log_Space: 178995864
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
驗證master

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 14
User: system user
Host:
db: NULL
Command: Connect
Time: 63424
State: Connecting to master
Info: NULL
*************************** 2. row ***************************
Id: 15
User: system user
Host:
db: NULL
Command: Connect
Time: 63424
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 25
User: slave1
Host: 10.64.5.170:47139
db: NULL
Command: Binlog Dump
Time: 62967
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 244
User: slave2
Host: 10.64.5.172:45629
db: NULL
Command: Binlog Dump
Time: 53898
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
16 rows in set (0.00 sec)
ERROR:
No query specified
4：驗證主從同步

在主庫server-1中創建一個表

mysql> USE cattle;
Database changed
mysql> CREATE TABLE test (name varchar(10) NULL ,old char(10) NULL );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| old | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
從庫查詢是否有這個新表

mysql> USE cattle;
Database changed
mysql> DESC test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| old | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

最後更新：2017-04-27 11:31:02

  上一篇：go mysql索引的詳細介紹
  下一篇：go 在CentOS 7上配置MySQL的主從複製模式 (Master-Slave Replication)