Shulou(Shulou.com)06/01 Report--

1.1 introduction to the principle of MySQL master-slave replication

The master-slave replication of MySQL is an asynchronous replication process (although it generally feels real-time). The data will be replicated from one MySQL database (we call it Master) to another MySQL database (we call it Slave). The whole process of master-slave replication between Master and Slave is completed by three threads, including two threads (SQL thread and IO thread) on the Slave side. The other thread (Imax O thread) is on the Masterside.

In order to realize the master-slave replication of MySQL, the binlog recording function on Masterside must be turned on first, otherwise it cannot be realized. Because the whole replication process is actually Slave takes the binlog log from the Master side, and then performs the various SQL operations inspired in the acquired binlog log on the Slave in the same order.

To turn on the binlog recording feature of MySQL, you can add the parameter option "log-bin" to the mysql module of MySQL configuration file my.cnf (the parameter section marked by [mysql]). The details are as follows:

[mysqld]

Log-bin = / data/3306/mysql-bin

The following is a summary of the key points of the principle of MySQL master-slave replication.

◆ master-slave replication is asynchronous logical SQL statement-level replication.

When ◆ is replicated, the master library has one I / O thread, and the slave library has two threads, Igamot O and SQL threads.

The necessary condition for ◆ to realize master-slave replication is that the master library should turn on the record binlog function.

The server-id of ◆ as a replicated MySQL node cannot be the same.

The ◆ binlog file records only SQL statements that make changes to the data (changes to the contents of the autonomous database), not any query (select,show) statements.

Forget the database password

Mysqld_safe-defaults-file=/data/3306/my.cnf--skip-grant-table-user=mysql & run in the background

Then log in without entering a password

Mysql-uroot-p-S / data/3306/mysql.sock

Set the password after entering the database

Update mysql.user setpassword=password ('oldboy123') where user='root' and host='localhost'

Refresh permissions

Flush privileges

1.2 MySQL Master-Slave replication practice

Environment: multiple instanc

10.0.0.52 3306

10.0.0.52 3307

3306-> 3307 copy-> 3309

-> 3008 replication

3306 Master-> 3307 from

Architecture practice: 3306-> 3307

1.2.1 Open the main library binlog and configure server-id administrators

[root@db02 ~] # egrep-I "server-id | log-bin" / data/3306/my.cnf

Log-bin = / data/3306/mysql-bin

Server-id = 6

Restart the service

/ data/3306/mysql restart

Slave library

[root@db02 ~] # egrep-I "server-id | log-bin" / data/3307/my.cnf

# log-bin = / data/3307/mysql-bin

Server-id = 7

1.2.2 the main library creates rep users

Grant replication slave on *. * to 'rep'@'172.16.1.%'identified by' oldboy123'

Mysql > grant replication slave on *. * to'rep'@'172.16.1.%' identified by 'oldboy123'

Query OK, 0 rows affected (0.04 sec)

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | root | 127.0.0.1 | |

| | rep | 172.16.1% | |

1.2.3 Export data from the main library

According to what we have seen, we can just take the backup at 00:00 today.

1. Lock the table flush table with read lock first

Mysql > flush table with read lock

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 120 | |

+-+

1 row in set (0.00 sec)

two。 The main library is complete.

Have all three important orders

Mysqldump

Cp/tar

Xtrabackup

Getting the location point is the key sed-n '22p' all_2017-06-28.sql

[root@db02] # mysqldump-B-- master-data=2-- single-transaction-S / data/3306/mysql.sock-A | gzip > / data/backup/all_$ (date+%F) .sql.gz

[root@db02 ~] # ls-l / data/backup/

Total dosage 228

-rw-r--r-- 1 root root 178468 June 28 11:11 all_2017-06-28.sql.gz

3. Main library unlocked

Mysql > unlock table

Query OK, 0 rows affected (0.00 sec)

1.2.4 Import full data from the library

[root@db02 scripts] # cd/data/backup/

[root@db02 backup] # gzip-d all_2017-06-28.sql.gz

[root@db02 backup] # mysql-S / data/3307/mysql.sock start slave

Query OK, 0 rows affected (0.03 sec)

Mysql > show slave status\ G

The following results are displayed to prove the success of master-slave replication practice.

[root@db02 backup] # mysql-S / data/3307/mysql.sock-e "show slavestatus\ G" | egrep "_ Running | Behind_Master" | head-3

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Seconds_Behind_Master: 0

#

◆ Slave_IO_Running: Yes, which is the state of the Yes thread. The thread reads the binlog log from the slave database to the master database and writes to the secondary log of the slave database. The status of Yes indicates that the IWeiO thread is working properly.

◆ Slave_SQL_Running: Yes, which is the SQL thread state. The SQL thread is responsible for reading the data in the relay log (relay-log) and translating it into SQL statements and applying it to the slave database. The state of Yes indicates that the SQL thread is working properly.

◆ Seconds_Behind_Master: 0, which is the number of seconds delayed by the slave database than the master database during replication. This parameter is very important, but a more accurate way to determine the master-slave delay in an enterprise is to write a timestamp in the master database, and then read the timestamp from the database to compare it with the current database time to determine whether it is delayed.

1.2.6 Log in to 3306 to view managed hosts

Mysql > showslave hosts

+-+

| | Server_id | Host | Port | Master_id | Slave_UUID | |

+-+

| | 7 | | 3307 | 6 | 295750c8-54c1-11e7-80dd-000c29fc02ee | |

| | 8 | | 3308 | 6 | 328e8c80-54c1-11e7-80dd-000c29fc02ee | |

+-+

2 rows in set (0.00 sec)

1.3 Summary of MySQL master-slave replication problems

Failure 1: the main library show master status; did not return a status result.

Mysql > show master status

Empty set (0.00 sec)

Answer: the reason for the above problem is that the binlog function switch is not turned on or does not work. The correct configuration results when binlog is enabled are as follows:

[root@db02 ~] # grep "log-bin" / data/3306/my.cnf

Log-bin = / data/3306/mysql-bin

[root@db02 ~] # mysql-uroot-poldboy123-S/data/3306/mysql.sock-e "showvariables like 'log_bin';"

Warning: Using a password on the command lineinterface can be insecure.

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

Fault 2: error message "Last_IO_Error:Got fatal error 1236 from master when reading datafrom binary log:'Could notfind first log file name in binary log index file'" appears

Answer: the reason for the above failure is that there is an error caused by an extra space in the value of a parameter when executing the CHANGE MASTER command, as follows:

CHANGE MASTER TO

MASTER_HOST='172.16.1.52'

MASTER_PORT=3306

MASTER_USER='rep'

MASTER_PASSWORD='oldboy123'

MASTER_LOG_FILE=' mysql-bin.000001', # / opt/$ (date +% F). Sql.gz

The master-data=1 parameter adds the following statement to the backup data

-- position to start replication or point-in-timerecovery from

Change master tomaster_log_file='mysql-bin.000005',master_log_pos=107

2) find the opportunity to import the complete slave library on the slave library that needs to be copied, and the command is as follows:

Gzip-d 2017-07-08.sql.gz

Mysql-uroot-poldboy123-S / data/3308/mysql.sock

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.