mysql master slave 1( 2015-11)

標籤：mysql master slave

註：環境為主庫單一實例，從庫多執行個體。

1. 主庫開logbin,確認server id 和從庫不同

[[email protected] backup]# egrep "log-bin|server-id" /etc/my.cnf

server-id= 1

log-bin=mysql-bin

[[email protected] backup]# egrep "log-bin|server-id" /data/3306/my.cnf 

#log-bin = /data/3306/mysql-bin

server-id = 06

2.主庫建使用者用於從庫訪問

[[email protected] backup]# mysql -uroot -p111111 -e"grant replication slave on *.* to ‘rep‘@‘%‘ identified by ‘111111‘;flush privileges;"

[[email protected] backup]# mysql -uroot -p111111 -e"show grants for rep"

+----------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]%                                                                                               |

+----------------------------------------------------------------------------------------------------------------+

| 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

mysql master slave 1( 2015-11)