Yundun practices MySQL series tutorial (4) [7 years of experience in flash sales LowB engineer] Master-slave replication and Backup Recovery Solution Production Environment practice, mysqllowb

Yundun practices MySQL series tutorial (4) [7 years of experience in flash sales LowB engineer] Master-slave replication and Backup Recovery Solution Production Environment practice, mysqllowb

Article 1: MySQL series of tutorials (I) production standard online environment installation and configuration cases and troubleshooting

Article 2: MySQL series of tutorials (II) the best database operations in history that you don't know

Article 3: MySQL series tutorials (3) view, trigger, stored procedure, functions, things, and statements that you do not know

Article 4: MySQL series of tutorials (4) master-slave replication, read/write splitting, simulated downtime, and Backup Recovery Solutions

 

Last year, the company had an engineer with seven years of PHP development experience and wanted to change jobs.

After an interview with a well-known Internet company in China, I had to tell him what master-slave replication was.

Well, I am very handsome at reading this article!

Yuan Fang, what do you think? Comment hard below.

You give me a thumbs up and I will teach you how to practice it!

 

Content of this chapter:

• Master-slave Replication
• Principles
• Back up the master database and restore the slave database. The configuration takes effect from the slave database.
• Read/write splitting
• What if the master node goes down?
• Dual-master Condition
• MySQL backup and recovery plan
• Back up one or more databases
• Common mysqldump Parameters
• How do I perform incremental recovery?
• Prerequisites for incremental recovery
• Mysqldump BACKUP command in the production environment
• Recovery

 

I. MySQL master-slave replication 1. Introduction

Master-slave replication is an excellent product in the production environment. If you don't, it's really LowB. But you're lucky to have read this article and get started with it to create a universe.

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 represent two machines respectively

# 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 & quot; log-bin | server-id & quot;/data/3306/my. cnf log-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 indicates that the function is successfully enabled. + ----------------------- + -------- +

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. sock Enter password: # Allow access from the 192.168.200 network segment of the database, 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 that this point is backed up. Mysql> show master status; + records + ------------- + records + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + records + ------------ + ------------------ + records + | mysql-bin.000013 | 410 | + records + ------------ + records -------------------- + ------------------------- + 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 the slave database, refer TO 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_D O_DB | Binlog_Ignore_DB | + bytes + ------------ + -------------------- + bytes + | mysql-bin.000013 | 410 | + bytes + ------------ + ------------------ + bytes + 1 row in set (0.00 sec) 5) unlock 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. sock Enter 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 = '2017. 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.info 18mysql-bin.0000131_192.168.200.98repnick330660001800.00004) takes effect! 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 mysq L 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 53 October 29 18:52 relay-log.info7) view the relay-log.info. [Root @ localhost 3307] # cat relay-log.info/data/3307/relay-bin.000002340mysql-bin.0000134978) view master.info. [Root @ localhost 3307] # cat data/master.info 18mysql-bin.42513497192.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 op Tion so it cannot execute this statement2) master-slave synchronization failure. 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 = 1007, # ignore fault No. server-id = 33) Enable bin-logvim my. cnflog-bin =/data/3307/mysql-binlog-slave-updates # indicates the slave database record bin-logexpire_logs_days = 7 # Keep 7 days 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.info mysql-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

I once worked in a well-known foreign company with 80 thousand employees in a 4-master and 8-slave architecture. The following shows the dual-master architecture. The situation is similar.

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

As a database administrator, the most important thing is the backup and recovery of the database, which is usually necessary for DBAs.

Let's take a look at our daily work!

1. Back up a single database

Basic backup of 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.bak drop 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®', 0, NULL), (3, 'ç' ¢ å ° ¼', 0, NULL), (4, 'å ° • å ° • ', 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 = MyISAM 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, which are hard to remember by every DBA

1.-B specifies multiple databases and adds the database creation statements and use statements. 2. -- compact removes comments, which is suitable for debugging output and is not required for production. 3.-A backs up all databases. 4.-F refresh the binlog. 5. -- add binlog log file names and corresponding location points to master-data. 6.-x, -- lock-all-tables 7,-l, -- locktables8,-d only backs up table structure 9,-t only backs up data 10, -- 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 increment means to add data on the basis of the original data. You do not have to add the data again each time, saving time and effort.

A: prerequisites for incremental recovery:

1. Enable the MySQL database log-bin parameter to record binlog logs. [Root @ localhost 3306] # grep log-bin/data/3306/my. cnf log-bin =/data/3306/mysql-bin2. The database is fully standby.

B: mysqldump BACKUP command in the production environment:

# Perform full database backup (the production environment also runs through scheduled tasks 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_FILE

C: recovery:

# Prohibit 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.sql2. refresh and back up 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 to. SQL format. [Root @ localhost backup] # Find the vim bin. SQL statement drop database nick to delete it !!! (Incorrect statement) 3. recover [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 !!!