MySQL master-slave replication and common error problems analysis

MySQL master-slave replication and common error problems analysis

First, master-slave Replication introduction:

1, MySQL master-slave replication principle:

MySQL Master-slave replication implementation, mainly rely on the binary log to achieve, the process is mainly based on the main MySQL data copied to the other host (Slave). During the copy process, it can be understood that a MySQL service acts as a server, while the other MySQL servers act as slave servers, and this slave server can be one or more. During the master-slave copy process, Mysql-master writes the update to the binary log and maintains an index of the file to track the log loop. On binary, the MySQL master server will install the binary file name you configured to generate binaries, and these logs are primarily used to record updates sent to the slave server. When a primary server is connected from the server, it notifies the primary server of the location of the last successful update read from the server in the log, and receives any updates from the server that have occurred since then. MySQL to do the master-slave replication, in fact, the transaction is recorded in the binary log, only need to get this log from the service, according to the log above the action on their own body can be. This enables the master-slave replication.

2. mysql binary replication type

MySQL master-slave replication can have three types of replication, namely: the copy of the statement Statemen, the row of replication rows and the mixed type of replication MIXED, The copy of the statement is the SQL statement executed on the primary server, and the same statement is executed from the server, and the replication of the row is the copy of the changed content, rather than the execution of the command from the server. By default, statement-based replication is used, and once a statement-based, imprecise replication is found, row-based replication, configuration, and replication types can be configured on the configuration file via Binlog_format =

3. mysql for the purpose of master-slave replication

MySQL Configures master-slave replication for data backup, high availability of data, and fault-tolerant lines

4, to realize the MySQL replication to pay attention to the details

1), each MySQL must have a unique server_id, and the primary server ID than all the server's ID to be smaller.

2), Master can have a lot of Slave, but each Slave can have only one Master

Second, MySQL master-slave replication implementation

1, configure the master-slave, open binary:

1) Master configuration:

#Cat/etc/my.cnf

[Mysqld] datadir=/var/lib/mysql log-bin=/var/lib/mysql/log-bin server-id = 1 Binlog_format = ' MIXED '

To restart the MySQL service:

#systemctl Restart MySQL

2) Slave configuration:

#Cat/etc/my.cnf

[Mysqld]datadir=/var/lib/mysqlserver-id = 3skip-grant-tableslog_slave_updates = 1read_only = 1

# #skip-grant-tables, log_slave_updates and read_only are my extra additions and can not be added. Just accompany the configuration Server-id.

#重启slave服务器

#systemctl Restart MySQL

2, the main library to view the binary:

Make a full backup first

# mysqldump-uroot--single-transaction-r--triggers-e--master-data--flush-logs--all-databases > Xiaozhang.sql

Enter Master to see if the log is enabled

Mysql> Show variables like ' log_bin '; +---------------+-------+| variable_name | Value |+---------------+-------+| Log_bin | On |+---------------+-------+1 row in Set (0.00 sec)

3. Create a sync user for the main library

Mysql> GRANT All on * * to ' rsync ' @ ' percent ' identified by ' 123456 ';mysql> FLUSH privileges;

4. View data nodes on the main library

Mysql> Show Master status;+----------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+--------------+------------------+-------------------+|      log-bin.000007 |              120 |                  |                   | |+----------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

5. Synchronizing from the top of the library

Mysql>change MASTER to master_host= ' 10.100.10.10 ', master_user= ' rsync ', master_password= ' 123456 ', master_log_file  = ' log-bin.000007 ', Master_log_pos=120;mysql>start slave; # #开启slave模式

6. See if synchronization is successful

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.200.6.51

Master_user:rsync

master_port:3306

Connect_retry:60

master_log_file:log-bin.000007

read_master_log_pos:120

relay_log_file:szthdb02-relay-bin.000002

relay_log_pos:281

relay_master_log_file:log-bin.000007

Slave_io_running:yes

Slave_sql_running:yes

# #可以发现Slave_IO_Running, and slave_sql_running have all been yes to indicate the success of master-slave replication configuration

Three, the configuration process common error analysis

1, slave_io_running for No

Check the logs and you'll find this error.

[ERROR] Slave I/o: Got fatal error 1236 from master If reading data from binary log: ' Could not ' find first log file name in Bina Ry Log index file '

Error Analysis:

This problem must be that the file name of your master_log_file is incorrect.

Workaround: Carefully check your master_log_file file name to see if you added a space when connecting.

2, slave_sql_running for No

Error analysis: Typically, the transaction is rolled back after the slave machine is reset.

Workaround:

mysql> stop slave;mysql> set GLOBAL sql_slave_skip_counter=1;mysql> start slave;

Iv. Adding Backup Scripts

The role of the script is to make an incremental backup every day from Monday to Friday, one weeks to do a fully prepared

#！ /bin/bash mysqldump ()  {mysqldump -uroot --single-transaction -R --triggers  -e --master-data --flush-logs --all-databases > /var/lib/mysql/' Date +%F '-%H: %M "' .sql }mysql  () {mysqladmin -uroot -p123456  flush-logs}crontab_dump  ()  {  cat /etc/crontab | grep  "bash $0 mysqldump "      if [ $? -eq 0 ]; then     echo  Task  was writen, it will  perform the task at 00:00 "   else     echo  "0 0 6 * *  bash $0  mysqldump  " >> /etc/crontab  fi     } crontab_ dumpcrontab_ mysqladmin  ()  {  cat /etc/crontab | grep  "bash  $0 mysql  "&Nbsp;   if [ $? -eq 0 ]; then     echo   "The port test has been written to the time task, and  00:00 will perform the task "  else     echo  "0 0 1-5 * *  bash $0 mysql "  >> /etc/crontab   fi     } crontab_ mysqladmin# #执行脚本后就基本实现了二进制 + Add-on + fully-prepared

V. Summary

The above is my implementation process, and the solution to the problem, you may think it is very simple, but when you configure the time, there will always be some small problems, because they are not careful and rigorous. So you should pay more attention in the future.

MySQL master-slave replication and common error problems analysis