MySQL master-Slave synchronization detailed configuration tutorial

8.10 Mysql Master-Slave synchronization

8.10.1 Master-Slave principle
The principle of MySQL master-slave synchronization:
1. Turn on the Bin-log log on master to record some records of changes deleted on master.
2, the master and slave each open IO thread, from the open IO thread and SQL thread. At the same time, the Serveid uniqueness of Master and slave is configured.
3, the Lord configured the authorized user, from the set Change Master Authorization connection command
3, from the upper IO thread through the authorization connection Master,master through the IO thread to check the Slav request log, Postsion point location.
4, Master sends these corresponding request content to Slave,slave receives, stores the content in the relay log realy_log inside, simultaneously generates writes a master-info, writes the log record and the Pos point. Records for the next connection
The point at which the log records are synchronized.
5. The slave SQL thread checks the contents of the Realy-log log update and parses the updated content into an SQL statement and then executes it locally to exec. The record is also written to the Realy-info
6, master-slave synchronization is an asynchronous way.

8.10.2 master-Slave synchronization status

Mysql> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:10.204.3.13
Master_user:tongbu
master_port:3306
Connect_retry:10
master_log_file:mysql-bin.000004
read_master_log_pos:239948407
relay_log_file:mysqld-relay-bin.000006
relay_log_pos:30462462
relay_master_log_file:mysql-bin.000004
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:239948407
relay_log_space:112003215
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:

Slave_io_running:yes
Slave_sql_running:yes
Both the IO thread and the SQL thread are Yes

master_log_file:mysql-bin.000004
relay_master_log_file:mysql-bin.000004
The primary log file and the file read from are the same

read_master_log_pos:239948407
exec_master_log_pos:239948407
The offset of the primary log is read and the offset of the primary log is equal

Show Processlist;

show slave status;

Sending Binlog event to slave;
Binary logs consist of various events, and an event is usually a new addition to some other information, and the thread has read an event from the binary log and is sending it to the slave server

Finished reading one binlog;switching to next Binlog

The thread has read the binary log file and opened the next log file to send to the slave server

Have sent all binlog to slave;waiting for Binlog to be updated
A thread has read all the major updates from the binary log and has been sent to a new event in the binary log that is now idle, waiting for a new update on the primary server to be delivered to the slave server.

Waiting to finalize termination
A very simple state that occurs when a thread stops

8.10.3 MySQL master-slave synchronization

(There are self-growing fields in MySQL, and you need to set up two dependent configurations that are self-growing when doing the primary master synchronization of the database:
In the primary master synchronization configuration, two servers need to be configured for auto_increment_increment growth of 2, and to configure the Auto_increment_offset to 1 and 2 respectively, This avoids a conflict between the values of the growth field when two servers are updating at the same time. )

? Configure Primary (Master)

1. VI/ETC/MY.CNF # Modify Add
   Server-id=1
   Log-bin=mysql-bin # mysql-bin Log prefix, customizable
   BINLOG-DO-DB=DB1,DB2 # Libraries that need to be synchronized
   BINLOG-IGNORE-DB=DB1,DB2 # Ignore out-of-sync libraries

Binlog two parameter 2 Select 1 do not write all sync
Query Bin-log whether to turn on show variables like '%log_bin% ';

2. Grant replication Slave on . to [e-mail protected] ' localhost ' identified by ' 123456 '; # Create a synced user
   Flush privileges;

3. Show master status; # Be sure to remember the first two columns of content,
   | File | Position | binlog_do_db |
   Mysql-bin.0000007 106

4. Do not exit MySQL at the same time, copy windows, using mysqldump to logically guarantee the consistency of the two databases, such as the main library export and import to the top.
Mysqldump-uroot-p123456-a-B--events--master-data=1-x >/backup/all.mysql

Plus read-only lock: Flush table with Locak;
Unlock: Unlocak tables;

? Set from (slave)

1. Vi/data/mysql/my.cnf

Server-id = 2 # This data cannot be the same as the Lord
The following optional parameters:
Replicate-do-db=db1,db2
Replicate-ignore-db=db1,db2

2. Go to MySQL execution: slave stop;

Change MASTER to
Master_host= ' master2.mycompany.com ',
Master_user= ' Replication ',
Master_password= ' Bigs3cret ',
master_port=3306,
Master_log_file= ' master2-bin.001 ',
Master_log_pos=4,
master_connect_retry=10;

3. Execution: Slave start;

4. View from the status from: Show slave status\g;

8.10.4 MySQL Master master sync
The concept of dual-machine hot standby simply say, is to maintain the status of two databases automatically synchronized. Operations on any one database are automatically applied to another database, keeping two of the database data consistent. There are many advantages to doing so. 1. A disaster can be made, one of which is broken can be switched to another. 2. Load balancing can be done to spread the request to any of the platforms to improve website throughput. For off-site hot preparation, especially for disaster preparedness.
The following architectures can be used to load balance the two masters, or you can use keeplived to achieve VIP access.

Resolving primary key self-growth variable conflicts

Master1:

Auto_increment_increment = 2 # The interval of the self-increment ID 1 3 5 interval is 2
Auto_increment_offset=1 # initial position of ID
Log-bin=mysql-bin
Log-slave-updates

Master2:
Auto_increment_increment = 2 # The interval of the self-increment ID 2 4 6 interval is 2
auto_increment_offset=2 # initial position of ID
Log-bin=mysql-bin
Log-slave-updates

There is a problem, may master2 when writing data, will not start from 2, may start from 6, after master1 the largest ID inserted, forming 6 8 10

In case the master-slave synchronization has been built:

8.10.5 Mysql master-slave Cascade

Open the Binlog scene from the library

1. Master-slave cascade mode from the time of serving as the main library
2. When you do a backup server

Open method

Log-bin=mysql-bin
Log-slave-updates
Expire_logs_days=7 Save Days

8.10.6 MySQL primary master sync failure
There are two no in the figure.
Slave_io_running:no
Slave_sql_running:no
Note that slave did not succeed, that is, the synchronization from B to a was unsuccessful. Let's go to the MySQL error log, where we said before:

To find the machine name. err file, open the look:

Look at the error message in the picture. said the trunk log file could not be found.
This is because we changed the relay file name when we configured the a relay, but MySQL was not synchronized. The solution is simple.

Stop the MySQL service first. Find these three files and delete them. Be sure to stop the MySQL service first. Otherwise it will not be successful. You need to restart the machine. or manually kill Mysqld.
OK, after starting MySQL. We're here to check the slave status:

8.10.7 Mysql one master more from

8.10.8 MySQL master-slave synchronization One-click Script implementation

Myuser=mysql
mypasswd=123456
Mysock=/usr/local/mysql/var/mysql.sock
Log_file=${datepath}/mysqllogs date +%F . Log
Data_file=${data_path}/mysqlbackup date +%F . sql.gz
Mysql_path=/usr/local/mysql/bin
mysql_cmd= "$MYSQL _path/mysql-u$myuser-p$mypasswd-s $MYSOCK"
mysql_dupm= "$MYSQL _path/dump-u$myuser-p$mypasswd-a-B--master-data=1--single-tarnsaction-e"

${mysql_dump} | gzip > $DATA _file

8.10.9 Mysql master-slave switch

8.10.9.1 Reset master and reset Slave

? RESET MASTER
Delete all the Binlog files recorded in the index file, empty the log index files, and create a new log file that is typically used only for the first time when the master library is used to build the master-slave relationship.

Attention
Reset master differs from two locations where purge binary log (clears binary logs)
1 Reset master Deletes all binlog files recorded in the log index file, creating a new log file starting at 000001, however the purge binary log command does not modify the values of the order in which the records Binlog
2 Reset master cannot be used for the main library with any slave running master-slave relationship. Because the Reset Master command is not supported at the slave runtime, reset master records the master's Binlog from 000001, and the slave recorded master log is the most recent binlog of the main library when reset master The specified Binlog file cannot be found by error from the library.

? RESET SLAVE
Reset slave will make slave forget the location information of the master-slave replication relationship. The statement will be used for clean startup, it deletes the Master.info file and the Relay-log.info file, and all the relay log files and re-enables a new Relaylog file.
You must use the Stop slave command to stop the replication process before you use the reset slave.

8.10.9.2 Master suddenly down the machine, how to recover:

1. Check the slave server
   Check the last state from the library to the main library
   Mysql> show Processlist;
   | 46 | Tongbu | web-1:58181 | NULL | Binlog Dump | 3221 | Have sent all binlog to slave; Waiting for Binlog to be updated
   If you see the above row state, the master-slave synchronization is normal

2. Compare POS points
   Query the show master status on the primary server, and the Pos point on the master.info is consistent with the latest POS point from the top
   If it is a master multiple from the case, view the information from the Master.info up-to-date, Pos point large, explain the update most complete, and then promote it to the main library.

3. Stop the IO thread from the library
   Make sure all relay log updates are complete
   The Stop slave;show processlist is executed on each slave library;
   Until you see the have read all relay log, which means that the update is done from the library

4. Upgrading from library to main library

A) First configure the/etc/my.cnf file to open Binlog
Comment out some log-slave-updates read-only
b) Clear the Master info and Mysql-bin files in the database directory
C) Execute the following command
Reset Master;

5. Add grant authorization on the new Main library
6. Show Master Status\g on the new main library; Viewing post points and Mysqlbin files
7. Log in from library
   Stop slave;
   Change Master to Masetr_host setting the main library
   Start slave;

8.10.9.3 planned switching master and slave

1. Lock table prevents new data writes to the primary database
2. Login from view status show Processlist; See if the final synchronization is complete
3. On the library from which you need to raise the main library,
   A) Modify the configuration file my.cnf
   Add Log-bin=mysql-bin
   b) Delete the local master.info
   Execution: Stop slave; Reset Master;
   c) Restart MySQL service
4. Add Grant authorization, show Master status state
5. Execute on all from the library
   Stop slave;
   Change Master to master_host= ' 10.204.1.200 ';
   Start slave;

8.10.9.4 The main library to be downgraded from the library
"Note" Switches the original repository to the step from the reset slave, which means to clear the local mysql-bin and mster-info information and clear the history of the master-slave relationship file

1. Stop the MYSQLD service, modify the configuration file, delete the Log-bin
2. Remove all Mysqlbin (binlog) files and master.info information from the MySQL directory
3. Start the service into MySQL to perform reset slave;
4. Restore the current master repository to the Slave repository (important, the data must be consistent)
5. In the current main library show Master Status\g; View log files and post points
6. Stop Slave;change master from the library; Start slave; show slave ststus\g; View status

8.10.1 master-Slave synchronization late

1. Fast restore of mistakenly deleted data and append Binlog
2. Late test

3. Historical queries (few scenes will be used)
   Stop slave
   Change Master to master-delay=600; Unit s S
   Start slave;
   Cancel, = 0;

   Show slave status\g;

This version can be configured at least 5.5

8.10.2 master-Slave synchronization failure
Mysql> Show Master Status\g;
1. Row
file:mysql-binlog.000001
position:309
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.00 sec)

ERROR:
No query specified

Got fatal error 1236 from master If reading data from binary log: ' Could not ' find first log file name in binary log Inde X file '

Cause: The file was incorrectly or was not in the correct format when it was change master, which prevented the file from being read

Workaround: Find the wrong place and change master again

Error message:
ERROR 1820 (HY000): Must SET PASSWORD before executing this statement

/etc/init.d/mysql stop

Mysql_salfe--user=mysql--skip-grant-tables--skip-networking &

Mysql-u Root MySQL
Mysql>update user Set Password=password (' newpasswd ') where user= ' root ';
Mysql>flush privileges;
Mysql>quit

Mysql>set PASSWORD = PASSWORD (' newpasswd ');

8.10.2.1 skipping a specified error based on the error code
? Method 1: Configure file Mode
In the configuration file, add the following parameters to automatically skip over some unimportant error codes
Vim/etc/my.cnf
--slave-skip-errors=1032,1062,1007
Failures that are generally caused by inbound duplication can be ignored

? Method 2: Command
The lock table is executed on the master side to prevent new data from being written
MySQL > Flush tables with read lock;

在salve端执行下列语句

Stop slave;
Set Global Sql_slave_skip_counter=1;s
Start slave;

8.10.2.2 master-Slave synchronization common error codes

1005: Failed to create table
1006: Failed to create database
1007: Database already exists, database creation failed <================= can be ignored
1008: Database does not exist, delete database failed <================= can be ignored
1009: Unable to delete database file causes database failure to be deleted
1010: Unable to delete data directory causes database failure to be deleted
1011: Failed to delete database file
1012: Cannot read records in system tables
1020: Record has been modified by another user
1021: Insufficient space on the hard drive, please increase the hard disk free space
1022: keyword Repeat, change record failed
1023: An error occurred while shutting down
1024: Read File error
1025: An error occurred while changing the name
1026: Write File Error
1032: Record does not exist <============================= can be ignored
1036: The data table is read-only and cannot be modified
1037: Insufficient system memory, please restart the database or restart the server
1038: Insufficient memory for sorting, increase the sort buffer
1040: The maximum number of connections to the database has been reached, please increase the number of available connections to the database
1041: Insufficient system memory
1042: Invalid host name
1043: Invalid connection
1044: The current user does not have permission to access the database
1045: Unable to connect to database, user name or password error
1048: field cannot be empty
1049: Database does not exist
1050: Data table already exists
1051: Data table does not exist
1054: field does not exist
1062: Duplicate field value, inbound failure <========================== can be ignored
1065: Invalid SQL statement, SQL statement is empty
1081: Unable to establish socket connection
1114: The data sheet is full and cannot hold any records
1116: Too many Open data tables
1129: Database is abnormal, please restart database
1130: Failed to connect to database, no permissions to connect to database is generally common is authorization error when authorization
1133: Database user does not exist
1141: The current user is not authorized to access the database
1142: The current user does not have permission to access the data table
1143: The current user does not have permission to access fields in the datasheet
1146: Data table does not exist
1147: User access to the data table is undefined
1149:sql statement Syntax error
1158: Network error, read error, please check network connection status
1159: Network error, read timeout, check network connection status
1160: Network error, write error, please check network connection status
1161: Network error, write timeout, please check network connectivity status
1169: Duplicate field value, update record failed
1177: Open Data table failed
1180: Commit TRANSACTION failed
1181: ROLLBACK TRANSACTION failure
1203: The current user and database establish a connection that has reached the maximum number of connections to the database, increase the number of available database connections or restart the database
1205: Lock timeout
1211: The current user does not have permission to create a user
1216: FOREIGN KEY constraint check failed, update child table record failed
1217: FOREIGN KEY constraint check failed, delete or modify master table record failed
1226: The current user is using more resources than allowed, please restart the database or restart the server
1227: Insufficient permissions, you do not have permission to do this
1235:mysql version is too low to have this feature

8.10.2.3 re-master from, fully synchronized
This method is suitable for the case that the master-slave database data is large or the data is completely unified.
The steps to resolve are as follows:
1) Enter the Mater, the lock table,
Mysql> flush tables with read lock;
Note: This is locked to a read-only state, and the statement is case insensitive
2) Perform data backup
[[email protected] MySQL] #mysqldump-uroot-p mysql> mysql.sql
3) View Master status
Mysql> Show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 272 | |
+-------------------+----------+--------------+-------------------------------+
4) Upload MySQL backup file to slave library for data recovery
[Email protected]]# SCP mysql.sql [email protected]:/tmp/
5) Stop the slave state
mysql> stop Slave;
6) Restore the database on slave
Mysql> Source/tmp/mysql.sql
7) Set up sync users from library
Change Master to Master_host = ' 192.168.1.12 ', master_user = root,master_password= ' 123456 ', master_log_file = ' Mysqld-bin .000001 ', master_log_pos=272;

8) re-open from sync
mysql> start slave;
9) View sync status
Mysql> Show slave status\g View:
Slave_io_running:yes
Slave_sql_running:yes
10) Unlock on master: Mysql>unlock tables;

8.10.2.4 after ignoring the error, continue syncing
This method is applicable to the case that the data of master-slave database is not very different, or the data can not be completely unified, the data requirements are not strict.
Solve:

1. Stop slave;
2. Set global sql_slave_skip_counter = 1; # 1 refers to jumping a step, you can customize how many steps
3. Start slave;
   Then use mysql> Show slave status\g to view:

8.10.2.5 database hard disk is broken, how to recover data?

1. Find a new device, restore a full backup of the data first,
2. After restoring the backup to the current Binlog log,

8.10.2.6 the server is not configured as slave; Fix in config file or with change MASTER to
Solution: Stop slave First, then change master, then start slave;

8.10.2.7 waiting to reconnect after a failed master event read
Log error Access denied; You need the REPLICATION SLAVE privilege for this operation

8.10.2.8 Got fatal error 1236 from master while reading data from binary log

Cause: May be in the Changemaster, the name of the specified Bin_log log written in the wrong format, such as more space, or write a typo, etc.

I experienced the method:

1. Restart the master side of the Mysqld service to view show Master Status\g; View the latest Post point
2. On slave, perform change master to master_log_file= ' mysql_bin.000005 ', master_log_pos=106;

Online Method 2:
On the source side, execute:
Flush logs;
Show master status;
Write down file, Position.
On the target side, execute:
Change MASTER to master_log_file= ' testdbbinlog.000008 ', master_log_pos=107;
Slave start;
Show slave status \g
Everything is fine.

8.10.2.9 MySQL synchronization problem how to solve?

8.10.2.10 MySQL What is the reason for synchronization delay, how to solve

1. How the MySQL database master-slave synchronization delay is generated.
   When the TPS for the main library is high, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and the delay is generated, and of course there is the possibility of a lock wait with slave's large query statement.
2. Network Latency
3. Master Load
4. Slave load
5. Poor hardware performance

It is common practice to use multiple slave to apportion read requests, and then take a dedicated server from these slave, only as a backup, without any other action, to achieve the ' real time ' requirement in relative maximum.

Optimization
A: The simplest solution to reduce the slave synchronization delay is to optimize the architecture and try to get the DDL of the main library to execute quickly. There is the main library is written, the data security is high, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1 and other settings, and slave do not need this high data security, fully can speak sync_ Binlog is set to 0 or off Binlog,innodb_flushlog can also be set to zero to increase the efficiency of SQL execution. The other is to use a better hardware device than the main library as a slave.

In addition, we introduce 2 parameters which can reduce the delay.
–slave-net-timeout=seconds
Parameter meaning: How long to wait to reestablish the connection and get the data when slave reads log data from the primary database
Slave_net_timeout units are set to seconds by default to 3,600 seconds
| Slave_net_timeout | 3600
–master-connect-retry=seconds
Parameter meaning: When the master-slave connection is re-established, if the connection establishment fails, how long after the interval is retried.
Master-connect-retry units are set to seconds by default to 60 seconds
Usually configuring the above 2 parameters can reduce the master-slave data synchronization delay caused by network problems

8.10.2.11 Host ' Mysql-slave ' isn't allowed to connect to this MariaDB server
Error connecting to master ' [email protected]:3306 '-retry-time:10 retries:86400 message:host ' mysql-slave ' are not all Owed to connect to this MariaDB server

180408 9:23:13 [ERROR] Slave I/o: Error connecting to master ' [email protected]:3306 '-retry-time:10 retries:86400 mess Age:host ' Mysql-slave ' isn't allowed to connect to this MariaDB server, error_code:1130

Fault reason: reported error code 1130 master-Slave status display IO has been processing can not connect, according to the code to determine that there is a problem authorizing the connection

Processing method: Query The main user information, to see if authorization is authorized when the user name and IP error, re-authorization

8.11 Mysql Half-sync

1, when the slave host is connected to master, it is able to see whether it is in a semi-synchronous replication mechanism.
2, there should be at least one slave to turn on the function of the semi-synchronous copy on master. At this point, a thread commits a transaction on master that will be blocked until it learns that a slave that has turned on the semi-synchronous copy has received all events for this transaction, or waits for a timeout.
3, when a transaction event has been written to its relay-log and has been flushed to disk, slave will not be informed of the receipt. On the master instance, there is a dedicated thread (Ack_receiver) that receives the response message from the repository and notifies the master repository of the logs that have been received by the notification mechanism, and can continue execution.
4, if the wait time-out, that is, master is not told that it was received, then Master will automatically convert to asynchronous replication mechanism. When at least one half-synchronized slave catches up, master and its slave automatically convert to a semi-synchronous replication mechanism.
5, the function of the semi-synchronous replication will be enabled in the Master,slave, semi-synchronous replication can only work; otherwise, only one side is turned on, and it is still replicated asynchronously. 6, the semi-synchronous feature, is to ensure that at any time the main data consistency problem. With respect to asynchronous replication, semi-synchronous replication requires that at least one repository be successfully received before it is returned to the user for each transaction that is required to execute.

Semi-synchronous deployment:

The ability to achieve semi-synchronous replication is simple, simply by installing a Google-provided plug-in from both the MySQL primary server and the server.
The semisync_master is used on the master service, which can be implemented from the server using the sosemisync_slave.so plug-in, which is within the MySQL Universal Binary Mysql/lib/plugin directory.
The configuration steps are as follows
1. Install the relevant plugins on the master and slave nodes respectively
Master
Install plugin:mysql> Install plugin rpl_semi_sync_master SONAME ' semisync_master.so ';
Boot module:mysql> set global rpl_semi_sync_master_enabled = 1;
Set timeout time:mysql> set global rpl_semi_sync_master_timeout = 1000;

Slave
Install plugin:msyql> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';
Boot module:mysql> set global rpl_semi_sync_slave_enabled = 1;
The restart process makes its module effective:mysql> stop slave io_thread; Start slave io_thread;

The above settings are dynamically set in the MySQL process, will take effect immediately but will be invalidated after restarting the service, in order to ensure the permanent effect, you need to write the relevant configuration to the main, from the server configuration file my.cnf:

Edit in master and slave my.cnf:
On Master
[Mysqld]
Rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 #此单位是毫秒

On Slave
[Mysqld]
Rpl_semi_sync_slave_enabled=1

Verify that the half-sync feature is enabled and view it by doing the following
Master
mysql> CREATE DATABASE asyncdb;
Master> Show status like ' Rpl_semi_sync_master_yes_tx ';
slave> show databases;

The test process is as follows

1. Create a database on the primary database, and you'll find that you'll soon create a
2. We test shutdown from the database, when the database is created on the primary database, the wait state is processed
   , when the timeout is created and, once timed out, the master switches from semi-synchronous to asynchronous synchronization,
3. When half-sync is turned on from on, the half-sync is resumed

8.12 Mysql Read-write separation amoeba Mysql-proxy mycal

MySQL read-Write separation implementation:

1. Through web Code
2. Through third-party software Mysql-proxy Amoeba

MySQL master-Slave synchronization detailed configuration tutorial