mysql master slave 1( 2015-11)
注：環境為主庫單實例，從庫多實例。
主庫開logbin,確認server id 和從庫不同
[root@db backup]# egrep "log-bin|server-id" /etc/my.cnf
server-id = 1
log-bin=mysql-bin
[root@db-2 backup]# egrep "log-bin|server-id" /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
server-id = 06
2.主庫建用戶用於從庫訪問
[root@db backup]# mysql -uroot -p111111 -e"grant replication slave on *.* to 'rep'@'%' identified by '111111';flush privileges;"
[root@db backup]# mysql -uroot -p111111 -e"show grants for rep"
+----------------------------------------------------------------------------------------------------------------+
| Grants for rep@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
3.主庫備份
mysqldump -uroot -p111111 -A -B --events -master-data=1 -x > /backup/db.sql
#備注:官方建議：鎖住表查看(flush table with read lock：show master status;show master logs;)--->備份--->解鎖(unlock tables):
4.從庫導入（已將數據scp過來）
mysql -uroot -p111111 -S /data/3306/mysql.sock </backup/db.sql
5.從庫change master,start slave。
change master to master_host='192.168.199.21', master_port=3306, master_user='rep', master_password='111111',master_log_file='mysql-bin.000025',master_log_pos=107;
start slave;
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.21
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000025
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
ERROR:
No query specified
