Detailed description of MySQL master-slave replication, read/write splitting, Backup recovery, mysql master-slave

Detailed description of MySQL master-slave replication, read/write splitting, Backup recovery, mysql master-slave

I. MySQL master-slave Replication

1. Introduction

Why do we need master-slave replication?

Master-slave replication objective:

• Real-Time Database Backup can be performed to ensure data integrity;
• Read/write splitting is supported. The master server only writes data and reads data from the server. This improves the overall performance.
  

Schematic:

It can be seen that synchronization is completed by synchronizing and reading log files.

2. Change the configuration file

The master id is usually smaller than the slave ID. Be sure to pay attention.

#3306 and 3307 respectively represent two machines # open log-bin and make the server-id different # vim/data/3306/my. cnflog-bin =/data/3306/mysql-binserver-id = 1 # vim/data/3307/my. cnflog-bin =/data/3307/mysql-binserver-id = 3 # Check 1. [root @ bogon ~] # Egrep "log-bin | server-id"/data/3306/my. cnflog-bin =/data/3306/mysql-binserver-id = 1 [root @ bogon ~] # Egrep & quot; log-bin | server-id & quot;/data/3307/my. cnf log-bin =/data/3307/mysql-binserver-id = 32, [root @ localhost ~] # Mysql-u root-p-S/data/3306/mysql. sock-e "show variables like 'Log _ bin';" Enter password: + -------- + | Variable_name | Value | + -------- + | log_bin | ON | # ON + -------- +

3. Create an account rep for slave Database Replication

A dedicated account for master-slave replication is usually created. Do not forget to grant permissions.

# Master database authorization: allow the slave database to connect to my Log retrieval [root @ localhost ~] # Mysql-uroot-p-S/data/3306/mysql. sockEnter password: # Allow access from the database 192.168.200 network segment, account rep, and password nick. Mysql> grant replication slave on *. * to 'rep '@' 192. 168.200.% 'identified by 'Nick '; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # Check the created rep account: mysql> select user, host from mysql. user; + ----- + ------------- + | user | host | + ----- + -------------- + | root | 127.0.0.1 | rep | 192.168.200.% | root | localhost. localdomain | + ----- + ---------------- + 7 rows in set (0.00 sec)

4. Back up the master database and restore it to the slave Database

Back up the existing data of the master database and restore it to the slave database. At this time, the data of the two hosts is consistent.

If you have data in advance, you cannot forget it.

1) Lock the master database so that only the read-only permission is allowed.

Mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) #5.1, 5.5 lock table commands are slightly different. #5.1 lock table: flush tables with read lock; #5.5 lock table: flush table with read lock;

2) Remember to back up data at this point.

mysql> show master status;+-------+------+--------+---------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------+------+--------+---------+| mysql-bin.000013 |  410 |       |        |+-------+------+--------+---------+1 row in set (0.00 sec)

3) clone the window and back up data.

[Root @ bogon ~] # Mysqldump-uroot-p-S/data/3306/mysql. sock-A-B -- events -- master-data = 2 | gzip>/opt/rep. SQL .gz Enter password: parameter:-: for details about all functions, see the rep. SQL .gz parameter vim/opt/rep. SQL .gz -- CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000013 ', MASTER_LOG_POS = 410;

4) check whether the master status is normal.

mysql> show master status;+------+------+---------+-------+| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------+-----+---------+--------+| mysql-bin.000013 |  410 |      |        |+--------+----+---------+--------+1 row in set (0.00 sec)

5) unlock the database

mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)

6) restore to slave Database

[root@bogon ~]# gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sockEnter password:

5. Configure slave database and take effect

Modify the connection parameters of the slave database and master database. The configuration takes effect. Check is successful!

1) Go to the slave database.

[root@bogon ~]# mysql -uroot -p -S /data/3307/mysql.sockEnter password:

2) change the parameters used by the slave server to connect to and communicate with the master server.

mysql> CHANGE MASTER TO   MASTER_HOST='192.168.200.98',   MASTER_PORT=3306,   MASTER_USER='rep',   MASTER_PASSWORD='nick',   MASTER_LOG_FILE='mysql-bin.000013',   MASTER_LOG_POS=410;Query OK, 0 rows affected (0.01 sec) 

3) view the changed parameters.

[root@localhost ~]# cd /data/3307/data/[root@localhost data]# cat master.info18mysql-bin.000013410192.168.200.98REPnick330660001800.0000

4) effective!

mysql> start slave;Query OK, 0 rows affected (0.01 sec)

5) Check the following parameters. If yes, it is normal!

Mysql> show slave status \ GRelay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes # Get the logo. Slave_ SQL _Running: Yes # Read relay-bin, logo, and write data. Seconds_Behind_Master: 0 # number of seconds behind the master database.

6) view relay-bin.logo.

[Root @ localhost 3307] # cd/data/3307 [root @ localhost 3307] # ll total usage 48drwxr-xr-x. 9 mysql 4096 October 29 18:52 data-rw-r --. 1 mysql 1900 October 29 11:45 my. cnf-rwx ------. 1 root 1307 October 20 17:06 mysql-rw ----. 1 mysql 6 October 29 11:00 mysqld. pid-rw-r -----. 1 mysql 15090 October 29 18:49 mysql_nick3307.errsrwxrwxrwx. 1 mysql 0 October 29 11:00 mysql. sock-rw ----. 1 mysql 150 October 29 18:49 relay-bin.000001-rw-rw ----. 1 mysql 340 October 29 18:52 relay-bin.000002-rw-rw ----. 1 mysql 56 October 29 18:49 relay-bin.index-rw-rw ----. 1 mysql October 29 18:52 relay-log.info

7) view relay-log.info.

[root@localhost 3307]# cat relay-log.info/data/3307/relay-bin.000002340mysql-bin.000013497

8) view master.info.

[root@localhost 3307]# cat data/master.info18mysql-bin.000013497192.168.200.98repnick330660001800.0000

6. read/write splitting

Read/write splitting exists in the production environment and is also a required skill.

Ignore MySQL Master/Slave replication authorization table synchronization and read/write splitting.

[Root @ bogon 3306] # vim my. cnf # Add the following four rows replicate-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = information_schemaserver-id = 1

1) the read-only parameter is used to prevent data from being written to the slave database.

# Modify the configuration file. Vim/data/3307/my. cnf [mysqld] read-only # The super or all privileges permission cannot be specified for user authorization. Otherwise it will be ineffective. # Create an account suoning and refresh the permissions. Mysql> grant select, insert, update, delete on *. * to 'suoning' @ 'localhost' identified by '000000'; Query OK, 0 rows affected (123 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # Log On with the create account and create the database [root @ bogon 3307] # mysql-usuoning-p123-S/data/3307/mysql. sockmysql> create user kangkangkang @ '2017.% 'identified by 'old123'; ERROR 1290 (HY000): The MySQL server is running with the -- read-only option so it cannot execute this statement

2) master-slave synchronization failure

A. last_ SQL _Errno: 1007 stop slave; set global SQL _slave_skip_counter = 1; start slave; B. ignore skip-name-resolve # ignore name resolution slave-skip-errors = 1032,1062, 1007 # ignore fault No. server-id = 3

3) Enable bin-log from the slave Database

Vim my. cnflog-bin =/data/3307/mysql-binlog-slave-updates # indicates the slave database record bin-logexpire_logs_days = 7 # retain the 7-day bin-log.

7. Master down

Let's simulate that if the host goes down, how can we minimize the loss from rapid replacement? Of course, dual-machine Hot Standby is also a good choice, so I will explain it to you in the next section.

In a master-slave multi-slave environment, if the master node is down, select the master node and continue to synchronize with other slave nodes.

A. Check the master.info of each slave database to see who is leading, up-to-date, larger, and has the least data loss. [Root @ localhost 3307] # cat/data/3307/data/master. infomysql-bin.000015326B. Make sure all relay logs are updated. Execute stop slave io_thread; show processlist on each slave database. If you know that The Has read all relay log is displayed, the slave database update is completed. C. log on to mysql-uroot-p-S/data/3307/mysql. sockstop slave; reset master; quitD. go to the database directory and delete the master.info relay-log.infocd/data/3307/data/rm-f master.info relay-log.infoE. 3307 upgrade to the master library vim/data/3307/my. enable log-bin =/data/3307/mysql-bin in cnf. If log-slave-updates or read-only exists, make sure to comment out. /Data/3307/mysql restartF. Other slave database operations stop slave; change master to master_host = '192. 168.200.98 '; start slave; show slave status \ G

8. Dual-Master

Use the primary master premise: the table's primary key auto-increment.

In the case of dual-master, the ID will be like this, And the ID is specified by the program to write the database M1: 1, 3, 5, M2: 2, 4, 6.

[root@localhost 3307]# vim my.cnf[mysqld]auto_increment_increment    = 2auto_increment_offset      = 2[root@localhost 3307]# ./mysql restart[root@localhost 3306]# vim my.cnf[mysqld]auto_increment_increment    = 2auto_increment_offset      = 1log-bin = /data/3306/mysql-binlog-slave-updates[root@localhost 3306]# ./mysql restart[root@localhost 3306]# mysqldump -uroot -pnick -S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql[root@localhost 3306]# mysql -uroot -pnick -S /data/3306/mysql.sock < /opt/3307bak.sqlmysql> CHANGE MASTER TO   MASTER_HOST='192.168.200.98',   MASTER_PORT=3307,   MASTER_USER='rep',   MASTER_PASSWORD='nick';mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status \G

Ii. MySQL backup and recovery

1. Back up a single database

The most basic backup for a single database.

1> Syntax: mysqldump-u username-p Database Name> backup database name 2> back up the nick_defailt database and view the content. [Root @ localhost ~] # Mysqldump-uroot-p-B nick_defailt>/opt/mysql_nick_defailt.bakEnter password: [root @ localhost ~] # Egrep-v "# | \ * | -- | ^ $"/opt/mysql_nick_defailt.bakDROP table if exists 'oldsuo '; create table 'oldsuo' ('id' int (4) not null, 'name' char (20) not null, 'age' tinyint (2) not null default '0', 'dept' varchar (16) default null) ENGINE = MyISAM default charset = latin1; lock tables 'oldsuo 'WRITE; insert into 'oldsuo' VALUES (2, 'ç' then comment®Comment ', 0, NULL), (3, 'ç' then degrees ° comment', 0, NULL), (4, 'degrees ° • degrees ° • ', 0, NULL); unlock tables; drop table if exists 'student '; create table 'student' ('qq' varchar (15) default null, 'id' int (4) not null AUTO_INCREMENT, 'name' char (20) not null, 'suo' int (4) default null, 'age' tinyint (2) not null default '0 ', 'dept' varchar (16) default null, 'sex' char (4) default null, primary key ('id'), KEY 'index _ name' ('name ')) ENGINE = MyISA M AUTO_INCREMENT = 8 default charset = latin1; lock tables 'student 'write; insert into 'student 'values (NULL, 2, 'oldsuo ', NULL, 0, NULL, NULL ), (NULL, 3, 'hangzhou', NULL, 0, NULL, NULL), (NULL, 4, 'hangzhou', NULL, 0, NULL, NULL), (NULL, 5, 'oldsuo ', NULL, 0, NULL, NULL), (NULL, 6, 'hangzhou', NULL, 0, NULL, NULL), (NULL, 7, 'hangzhou', NULL, 0, NULL, NULL); unlock tables; 3> enable compression backup database [root @ localhost ~] # Mysqldump-uroot-p-B nick_defailt | gzip>/opt/mysql_nick_defailt.bak.gzEnter password: [root @ localhost ~] # Ll/opt/total usage 28-rw-r -- r --. 1 root 2109 October 24 16:36 data_bak.sq2-rw-r -- r --. 1 root 2109 October 24 16:36 data_bak.sql-rw-r -- r --. 1 root 1002 October 27 11:55 mysql_nick_defailt.bak-rw-r -- r --. 1 root 1002 October 27 11:56 mysql_nick_defailt.bak.gz-rw-r -- r --. 1 root 3201 October 27 11:46 mysql_nick_defailt_ B .bakdrwxr-xr-x. 2 root 4096 November 22 2013 rh-rw-r --. 1 root 1396 October 24 Student_bak.sql4> recover the nick_defailt database [root @ localhost ~] # Mysql-uroot-p nick_defailt </opt/mysql_nick_defailt.bak Enter password: # Add-B restore method [root @ localhost ~] # Mysql-uroot-p </opt/mysql_nick_defailt_ B .bak Enter password: 5> Conclusion 1. Backup with the-B parameter. Add use db and create database information. 2. Use gzip to compress the backup data.

2. Back up multiple databases

What about backing up multiple databases?

# Add space in the middle of multiple database Names [root @ localhost ~] # Mysqldump-uroot-p-B nick_defailt oldsuo oldsuo_1 | gzip>/opt/mul. SQL .gz Enter password:

3. Back up one or more tables

What should I do if I back up one or more tables?

1> Syntax: mysqldump-u user name-p database name Table Name> Backup file name [root @ localhost ~] # Mysqldump-uroot-p nick_defailt student>/opt/mysql_nick_defailt_student.bakEnter password: 2> Syntax: mysqldump-u user name-p database name table name 1 Table name 2> Backup file name [root @ localhost ~] # Mysqldump-uroot-p nick_defailt student oldsuo>/opt/mysql_nick_defailt.bak Enter password:

4. mysqldump Parameters

Key parameters of mysqldump

-B specifies multiple databases and adds the database creation statements and use statements. -- Compact removes comments, which is suitable for debugging output and is not required for production. -A: Back up all databases. -F refresh the binlog. -- Add binlog log file names and corresponding location points to master-data. -X, -- lock-all-tables-l, -- locktables-d only backs up the table structure-t only backs up data -- single-transaction is suitable for innodb transaction Database Backup.

5. incremental recovery

It is important that the production environment is generally Incremental backup and recovery. The so-called incremental means to add data on the basis of the original data, without having to re-add each time, saving time and effort.

A: prerequisites for incremental recovery: 1. Enable binlog for logging the MySQL database log-bin parameter. [Root @ localhost 3306] # grep log-bin/data/3306/my. cnflog-bin =/data/3306/mysql-bin2. The database is fully standby. B: mysqldump BACKUP command in the production environment: # Full backup of the database (the production environment also runs through the scheduled task every morning) mysqldump-uroot-pnick-S/data/3306/mysql. sock -- default-character-set = gbk -- single-transaction-F-B nick | gzip>/server/backup/mysql _ $ (date when using f0000. SQL .gz # innodb Engine backup mysqldump-u $ MYUSER-p $ MYPASS-S $ MYSOCK-F -- single-transaction-A-B | gzip> $ DATA_FILE # myisam engine backup mysqldump-u $ MYUSER-p $ MYPASS-S $ MYSOCK-F-A-B -- lock-all-tables | gzip> $ DATA_FIL EC: Recovery: # prohibit applications such as web applications from writing data or locking tables to the master database through the firewall. Temporarily stop the updates of the master database and restore the database. # Deleting the nick library by mistake! 1. check the full backup and binlog [root @ localhost 3306] # cd/server/backup/[root @ localhost backup] # gzip-d mysql_2015-10-31. SQL .gz [root @ localhost backup] # vim mysql_2015-10-31. SQL [root @ localhost backup] # grep-I "change" mysql_2015-10-31. SQL

2. Refresh immediately and back up the binlog

[Root @ localhost 3306] # mysqladmin-uroot-pnick-S/data/3306/mysql. sock flush-logs [root @ localhost 3306] # cp/data/3306/mysql-bin.000030/server/backup/# misoperation log-bin, penultimate [root @ localhost backup] # mysqlbinlog-d nick mysql-bin.000030> bin. SQL # export. SQL format. [Root @ localhost backup] # Find the vim bin. SQL statement drop database nick to delete it !!! (Incorrect statement)

3. Restore

[Root @ localhost backup] # mysql-uroot-pnick-S/data/3306/mysql. sock <mysql_2015-10-31. SQL # restore the database before full backup [root @ localhost backup] # mysql-uroot-pnick-S/data/3306/mysql. sock nick <bin. SQL # restore and delete the bin-log of the misoperation language. # Done !!!

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!