Master-Slave replication principle
MySQL replication is an asynchronous replication process that replicates from master to one or more slave.
The entire replication process between master and slave is done primarily by three threads, one IO thread on the master side and two threads (SQL thread and IO thread) on the slave side.
The ability to open binary log files via the master server, slave the log information from the master, and then parse the binaries into SQL statements and execute the various operations recorded by the SQL statement in full order. (slave acquired binary files are also written to their own relay log file)
Replication concept
MySQL replication technology is a log copy process in which a server acts as the primary, one or more other servers acting as slave servers during the replication process;
The data consistency is ensured by pulling the binary log file from the server to the master server, parsing the log file into the appropriate SQL statement, and then re-performing the operation of the master server from the server.
Master-Slave Replication configuration steps:
Set Server-id (server identity, which cannot be duplicated in a group of hosts)
Turn on the binary log and specify the path to save the binary log file
Record Bin-log files and bin-log (position) locations
If you keep on master, add a global lock, backup the database that needs to be synchronized to the slave node, and unlock the global lock.
Create a user for synchronous replication
Using change master to establish a connection between slave and master (slave node setting master server)
Start slave
Check the status of the slave
STEP1: Configuring/ETC/MY.CNF files for Master and slave
# #Master [mysqld]basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306socket=/var/lib/mysql/ Mysql.sockserver-id=1 #服务标识log-bin=/data/mysql/binlog/mysql-bin #binlog日志文件保存的路径binlog-cach e-size=10m #binlog日志缓存大小sync-binlog=1 #每隔N秒将缓存中的二进制日志记录写回硬盘expire_logs_days =30 #二进制日志文件过期时间 (automatic cleanup time)
# #Slave [mysqld]basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306socket=/var/lib/mysql/ mysql.sockserver_id=2relay-log=/data/mysql/binlog/mysql-relay-binreplicate-wild-do-table=testdb1.% # Specifies the database to be synchronized replicate-wild-do-table=testdb2.% #指定需要同步的数据库 # (corresponding to the replicate-w ild-ignore-table)
STEP2: Manually synchronizing databases to slave
1. Write operations on tables that lock master (do not exit the terminal)
Mysql> flush tables with read lock; Query OK, 0 rows Affected (0.00 sec)
2. Back up the database on master and upload to Slave
[Email protected] ~]# mysqldump-uroot-predhat testdb1 >/root/testdb1.sql[[email protected] ~]# tar zcf testdb1.tar. GZ Testdb1.sql[[email protected] ~]# rsync-av/root/testdb1.tar.gz 192.168.1.211:/root/[[email protected] ~]# scp/root/ testdb1.tar.gz [Email protected]:/tmp/
3. Create a synchronized database on slave and import the data file
[[email protected] ~]# mysql-uroot-predhat-e ' CREATE Database testdb1 ' [[email protected] ~]# mysql-uroot-predhat test DB1 < Testdb1.sql mysql> show tables;+-------------------+| TABLES_IN_TESTDB1 |+-------------------+| TT1 | | TT2 |+-------------------+
Step3:master Creating a synchronized user
1, Master Unlock
mysql> unlock tables;
2. Create a sync user
mysql> grant replication Slave on * * to ' repl_user ' @ ' 192.168.1.211 ' identified by ' repl_passwd ';mysql> flush Privil Eges;
Step4:slave Connect master, set Node1 to its home server
1. View Master's Master-log-file file and position location
Mysql> Show Master status;+------------------+----------+--------------+------------------+------------------- +| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+
2. Connect master on Slave, and start slave
mysql> Change Master tomaster_host= ' 192.168.1.210 ', master_user= ' repl_user ', master_password= ' repl_passwd ', Master_port=3306,master_log_file= ' mysql-bin.000001 ',master_log_pos=120;mysql> start slave;
3. View Slave status on Slave (slave_io_running,slave_sql_running, and Seconds_behind_master )
mysql> show slave status\g;*************************** 1. row *************** slave_io _state: waiting for master to send event Master_Host: 192.168.1.210 master_ user: repl_user 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: 283 relay_master_log_file: mysql-bin.000001 Slave_IO_Running: Yes slave_sql_running: yes replicate_do_db: replicate_ignore_db: Replicate_Do_Table: replicate_ignore_table: replicate_wild_ do_table: testdb1.%,testdb2.% replicate_wild_ignore_table: Last_Errno: 0 Last_Error: skip_counter: 0 Exec_Master_Log_Pos: 471 Relay_Log_Space: 807 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
STEP5: Test master-Slave synchronization function
1. Creating databases and tables on master
mysql> INSERT INTO TT1 (id,name) VALUES (1, ' Hoai '), (2, ' dime ');mysql> CREATE database testdb2;
2, slave on check whether synchronization
mysql> select * from tt1;+------+------+| id | name |+------+------+| 1 | hoai | | 2 | dime |+------+------+2 rows in set (0.00 SEC) mysql> show databases;+--------------------+| database |+--------------------+| information_schema | | mysql | | performance_schema | | test | | testdb1 | | testdb2 |+--------------------+6 rows in set (0.00 sec)
Clear the Binary log method (reset master/slave):
mysql> Reset Master;
mysql> reset Slave; (for relay-log files from the top)
MySQL Replication Master-slave replication-(instance)