MySQL5.6 GTID方式,配置主从
迁移数据到从库
数据导出: mysqldump -uroot -p111111 -h127.0.0.1 -P3306 -q --single-transaction -R -E --triggers --default-character-set=utf8 -B db3306 > db3306.sql //单库备份 mysqldump -uroot -p111111 -h127.0.0.1 -P3306 -q --single-transaction -R -E --triggers --default-character-set=utf8 --all-databases> all.sql //全库备份
mysql -e "show databases;" -uroot -p111111 | grep -Ev "Database|information_schema|performance_schema|mysql" | xargs mysqldump -uroot -p111111 --databases {} > mysql_dump.sql
数据导入: [root@mysql-1 mysql]# mysql -uroot -p111111 -h127.0.0.1 -P3307 < db3306.sql
配置GTID主从环境
编辑/etc/my.cnf,同时在master和slave上,添加如下内容: log-bin =mysql-bin binlog_format =row log_slave_updates gtid-mode =ON enforce-gtid-consistency=ON
$ cat /etc/my.cnf
[mysql@mysql-1 ~]$ cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [client] port =3306 socket =/var/lib/mysql3306/mysql.sock [mysqld_multi] mysqld=/usr/bin/mysqld_safe mysqladmin=/usr/bin/mysqladmin user=root password=123456 log=/var/log/multi.log [mysqld1] server-id=1 port = 3306 datadir =/var/lib/mysql3306 socket =/var/lib/mysql3306/mysql.sock pid-file = /tmp/mysqld1.pid symbolic-links =0 sql_mode =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #配置GTID主从环境 log-bin =mysql-bin binlog_format =row log_slave_updates gtid-mode =ON enforce-gtid-consistency=ON [mysqld2] server-id=2 port = 3307 datadir =/var/lib/mysql3307 socket =/var/lib/mysql3307/mysql.sock pid-file = /tmp/mysqld2.pid symbolic-links =0 sql_mode =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #配置GTID主从环境 log-bin =mysql-bin binlog_format =row log_slave_updates gtid-mode =ON enforce-gtid-consistency=ON [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
建立一个复制用户
登录主库: mysql -uroot -p111111 -h127.0.0.1 -P3306 建立一个复制用户: mysql>grant replication slave, replication client on *.* to repl@'localhost' identified by 'repl'; mysql>flush privileges;
从库配置命令
登录从库: mysql -uroot -p111111 -h127.0.0.1 -P3307 > stop slave; > change master to master_host='localhost', master_port=3306, master_user='repl', master_password='repl'; > start slave;
主库
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000003 Position: 191 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 2f76444d-171e-11e6-a99b-080027af21c4:1-3 1 row in set (0.00 sec) mysql> show processlist\G *************************** 1. row *************************** Id: 6 User: repl Host: localhost:32997 db: NULL Command: Binlog Dump GTID Time: 1922 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 7 User: root Host: localhost:46938 db: db3306 Command: Query Time: 0 State: init Info: show processlist 2 rows in set (0.00 sec)
从库
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 854 Relay_Log_File: mysqld2-relay-bin.000004 Relay_Log_Pos: 1064 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 854 Relay_Log_Space: 1647 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 Master_UUID: 2f76444d-171e-11e6-a99b-080027af21c4 Master_Info_File: /var/lib/mysql3307/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 2f76444d-171e-11e6-a99b-080027af21c4:1-6 Executed_Gtid_Set: 2f76444d-171e-11e6-a99b-080027af21c4:1-6, 62daf5cb-1724-11e6-a9c3-080027af21c4:1 Auto_Position: 1 1 row in set (0.00 sec)