MySQL master-Slave Introduction, preparation, preparation, configuration from, test master-Slave synchronization

17.1 MySQL Master-slave introduction

• MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, the data in real-time synchronization;
• MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.
• The master-slave process has a roughly 3-step
  • The change operation is recorded in Binlog.
  • From synchronizing the main binlog event (SQL statement) to the native and recording in Relaylog
  • Execute sequentially from the SQL statements inside the Relaylog
• The Lord has a log dump thread that is used to communicate with the I/O thread from Binlong
• There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to put the SQL statements inside the Relaylog
  MySQL master-slave schematic diagram:
  

17.2 preparatory work

• Install MySQL

Note:

#设置开机启动[[email protected] ~]# chkconfig mysqld on

Please refer to previous article: http://blog.51cto.com/3622288/2056837 12.2 Bar

17.3 Configuring the Master

• Modify MY.CNF

[[email protected] ~]# vi /etc/my.cnf#增加server-id=130和log_bin=taoyuansocket=/tmp/mysql.sock #如下增加server-id=12 #可以自定义，如设定为IP地址192.168.0.12 中的12log_bin=taoyuan

• After modifying the configuration file, start or restart the Mysqld service

[[email protected] ~]# /etc/init.d/mysqld restart;#查看文件[[email protected] ~]# cd /data/mysql/[[email protected] mysql]# ls -lt-rw-rw----  1 mysql mysql       34 1月  23 16:57 taoyuan.index-rw-rw----  1 mysql mysql      120 1月  23 16:57 taoyuan.000002-rw-rw----  1 mysql mysql      143 1月  23 16:57 taoyuan.000001#上述文件，必须有，不然主从无法完成

• MySQL can be backed up and restored to Taoyuan library as test data
  • Mysqldump-uroot mysql >/tmp/mysql.sql
  • MYSQL-UROOT-E "CREATE Database Taoyuan"
  • Mysql-uroot Taoyuan </tmp/mysql.sql
• Create a user to use to synchronize data

#创建用户mysql> grant replication slave on *.* to ‘repl‘@‘192.168.0.10‘ identified by ‘taoyuan‘;Query OK, 0 rows affected (0.00 sec)#锁表，防止再次写入数据mysql> flush tables with read lock;Query OK, 0 rows affected (0.03 sec)#记住位置mysql> show master status;+----------------+----------+--------------+------------------+-------------------+| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------------+----------+--------------+------------------+-------------------+| taoyuan.000002 |   660574 |              |                  |                   |+----------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

17.4 Configuration from

• Modifying a configuration file

[[email protected] ~]# vi /etc/my.cnf#增加server-id 跟主不一样 可以设置成10#log_bin 不需要设置，主才需要生成二进制文件，从不用#重启服务[[email protected] ~]# /etc/init.d/mysqld restart

• Data synchronization

#采用复制虚拟机操作，如果没有可以用如下的命令进行同步scp 192.168.0.12:/tmp/*.sql /tmp/#恢复库mysql> create database taoyuan;Query OK, 1 row affected (0.00 sec)mysql> create database blog;Query OK, 1 row affected (0.01 sec)

• Implement Master-Slave

mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host=‘192.168.0.12‘, master_user=‘repl‘, master_password=‘taoyuan‘, master_loog_file=‘taoyuan.000002‘, master_log_pos=660574;Query OK, 0 rows affected, 2 warnings (0.04 sec)#填写show master status; 显示的信息mysql> start slave;Query OK, 0 rows affected (0.01 sec)

• See if Master and slave are configured successfully

mysql> show slave status\G#还需到主上执行 unlock tables;

17.5 testing master-Slave synchronization

• Lord Mysql-uroot Taoyuan
• Select COUNT (*) from DB;
• TRUNCATE TABLE db;
• To from the upper Mysql-uroot aming;
• Select COUNT (*) from DB;
• The Lord continues to drop table db;

• View the DB table from the top

• Several configuration parameters
  • On the primary server
  • binlog-do-db=//Synchronize only the specified libraries
  • binlog-ignore-db=//Ignore specified library
  • From the server
  • replicate_do_db=
  • replicate_ignore_db=
  • replicate_do_table=
  • replicate_ignore_table=
  • The following two common
  • replicate_wild_do_table=//As taoyuan.%, wildcard% supported
  • replicate_wild_ignore_table=

MySQL master-slave configuration UUID Same error resolution

When configuring MySQL master and slave, because it is a copy of the MySQL directory, resulting in the same master-slave MySQL uuid, slave_io can not start, error message as follows:

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must is different for replication to work.
Workaround: Modify the value of the UUID in the auto.cnf file in MySQL data directory, make the two MySQL different, modify and restart the MySQL service.

MySQL master-Slave Introduction, preparation, preparation, configuration from, test master-Slave synchronization