Detailed Mysql master-slave copy, read-write separation, backup recovery _mysql

First, MySQL master and slave copy

1. Introduction

Why do we use master-slave replication?

Master-Slave replication purposes:

• Can do real-time backup of the database to ensure the integrity of the data;
• Can do read and write separation, master server just write, read from the server, so you can improve the overall performance.
  

Schematic diagram:

As can be seen from the above diagram, synchronization is by log file synchronization read and write completed.

2, change the configuration file

Two-day machines operate to ensure that server-id are different, usually with a primary ID less than the ID. Be sure to pay attention.

# 3306 and 3307 stand for 2 machines
# Open Log-bin and make Server-id different
#vim/data/3306/my.cnf
log-bin =/data/3306/mysql-bin
Server-id = 1
#vim/data/3307/my.cnf
log-bin =/data/3307/mysql-bin
Server-id = 3
#检查
1,
[Root@bogon ~]# egrep "Log-bin|server-id"/data/3306/my.cnf log-bin
=/data/3306/mysql-bin
Server-id = 1
[Root@bogon ~]# egrep "Log-bin|server-id"/data/3307/my.cnf log-bin 
=/data/3307/mysql-bin
Server-id = 3
2,
[root@localhost ~]# mysql-uroot-p-s/data/3306/mysql.sock-e "show variables like ' Log_bin '; "
Enter Password:
+--------+--------+
| variable_name | Value |
+--------+--------+
| Log_bin    | On |  # on to start opening success
+--------+--------+

3, set up to be used to copy from the library account rep

You will typically create a dedicated account for master-slave replication, and do not forget to authorize it.

# Master Library authorization, allow me to connect from the library log
[root@localhost ~]# mysql-uroot-p-s/data/3306/mysql.sock
Enter password:
# Allow connection from library 192.168.200 network segment, account rep, 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 rep account created:
mysql> select User,host from Mysql.user;
+-----+-------------+
| user | host       |
+-----+--------------+
| root | 127.0.0.1     |
| rep | 192.168.200.% | |
root | localhost |
| | root | localhost.localdomain
| +-----+------------------+
7  rows in Set (0.00 sec)

4, backup the main library, and restore to from the library

Back up the main library's existing data and restore it to the data from the library, at which time two hosts are consistent.

If there is data in advance, this can not be forgotten.

1 lock on the main library so that only read-only permissions are 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 tables: Flush table with read lock;

2 Remember this is the point of backup.

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 the data.

[Root@bogon ~]# mysqldump-uroot-p-s/data/3306/mysql.sock-a-B--events--master-data=2|gzip >/opt/rep.sql.gz
   
    enter Password:
parameters:  -A: Back up all
#看rep. sql.gz parameters
vim/opt/rep.sql.gz-
-Change Master to master _log_file= ' mysql-bin.000013 ', master_log_pos=410;
   

4 View master status, the value 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) Jian Ku

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

6) revert to from the library

[Root@bogon ~]# gunzip </opt/rep.sql.gz | Mysql-uroot-p-s/data/3307/mysql.sock
Enter Password:

5, configuration from the library and effective

Change the connection parameters from the library and the Master library, and the configuration takes effect. The inspection was successful!

1) Enter from the library.

[Root@bogon ~]# mysql-uroot-p-s/data/3307/mysql.sock
Enter Password:

2 Change the parameters that the secondary server uses to connect and communicate with the primary 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;

3 View the changed parameters.

[Root@localhost ~]# cd/data/3307/data/
[root@localhost data]# cat master.info
mysql-bin.000013
410
192.168.200.98
REP
Nick
3306
0
0
1800.000
0

4) Effective!

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

5) Check the following parameters, it is normal to comply with!

Mysql> show slave status\g
relay_master_log_file:mysql-bin.000013
       slave_io_running:yes    #取logo.
      slave_sql_running:yes    #读relay-bin, logo, write data.
seconds_behind_master:0    #落后主库的秒数.

6) View Relay-bin.logo.

[Root@localhost 3307]# cd/data/3307
[root@localhost 3307]# ll
total dosage of
drwxr-xr-x. 9 MySQL mysql 4096 October 29 18 : Data
-rw-r--r--. 1 mysql mysql 1900 October 11:45 my.cnf
-rwx------. 1 root root  1307 October 17:06 Mysql
   -RW-RW----. 1 MySQL MySQL   6 October 11:00 mysqld.pid
-rw-r-----. 1 mysql mysql 15090 October 18:49 mysql_nick3307.err srwxr
Wxrwx. 1 MySQL mysql   0 October 11:00 mysql.sock
-rw-rw----. 1 mysql mysql  150 October 18:49 relay-bin.000001-
R W-RW----. 1 MySQL MySQL  340 October 18:52 relay-bin.000002
-rw-rw----. 1 mysql mysql  56 October 18:49 relay-bin.index
   -RW-RW----. 1 MySQL MySQL  53 October 18:52 Relay-log.info

7) View Relay-log.info.

[Root@localhost 3307]# cat relay-log.info
/data/3307/relay-bin.000002
mysql-bin.000013
497

8) View Master.info.

[Root@localhost 3307]# cat data/master.info
mysql-bin.000013
497
192.168.200.98 Rep Nick
3306
0
0
1800.000
0

6, read and write separation

Read and write separation in the production environment abound, but also the necessary skills.

Ignore MySQL master-slave copy Authorization table synchronization, read-write separation.

[Root@bogon 3306]# vim my.cnf
#添加以下四行
replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = Performance_schema
binlog-ignore-db = information_schema
Server-id = 1

1 by read-only parameters to prevent data from being written to the library from the method.

#修改配置文件.
vim/data/3307/my.cnf
[Mysqld]
Read-only
#对用户授权事不能指定有super或all Privileges permissions. otherwise no effect.
#创建账户suoning, and refresh the permissions.
mysql> Grant Select,insert,update,delete on *.* to ' suoning ' @ ' localhost ' identified by ' 123 ';
Query OK, 0 rows Affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows Affected (0.00 sec)
#用创建账户登录, and create a library
[Root@bogon 3307]# mysql-usuoning-p123-s/data/3307/mysql. Sock
mysql> Create user kangkangkang@ ' 192.% ' 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. Ignoring
skip-name-resolve    #忽略名字解析
slave-skip-errors = 1032,1062,1007  #忽略故障编号
Server-id = 3

3 Open Bin-log from the library

Vim my.cnf
log-bin =/data/3307/mysql-bin
log-slave-updates      #表示从库记录bin-log
expire_logs_days = 7    #保留7天bin-log.

7. Main downtime

Let's simulate, if the mainframe is down, how do we get the quick replacements and minimize the damage? Of course, two-machine hot standby is also a good choice, the next section will explain to you.

A master more from the environment, if the main downtime, choose one from the master, continue and other from sync.

A. Look at each master.info from the library to see who is more forward, latest, larger, and lost the least amount of data.
[root@localhost 3307]# cat/data/3307/data/master.info
mysql-bin.000015
326
B. Ensure that all relay logs are fully updated.
executes the stop slave io_thread;show processlist from the library;
Know that seeing has read all relay log indicates that the update is done from the library.
C. Landing mysql-uroot-p-s/data/3307/mysql.sock
stop slave;
Reset Master;
Quit
D. Go to the database directory, delete master.info relay-log.info
cd/data/3307/data/
rm-f master.info
E. 3307 promote the main library
vim/data/3307/my.cnf
Open log-bin =/data/3307/mysql-bin
If there is log-slave-updates, Read-only and so on must be commented.
/data/3307/mysql Restart
F. Other operations from the library
stop slave;
Change Master to Master_host = ' 192.168.200.98 ';
Start slave;
Show Slave Status\g

8. Double Main

Use the main premise: the table's primary key is self increasing.

In a dual-master case, the ID will be the case, with the program specifying the ID write library m1:1,3,5,m2:2,4,6.

[Root@localhost 3307]# vim my.cnf
[mysqld]
auto_increment_increment    = 2
auto_increment_offset      = 2
[root@localhost 3307]#./mysql restart
[root@localhost 3306]# vim my.cnf
[mysqld]
Auto_increment_ Increment    = 2
auto_increment_offset      = 1
log-bin =/data/3306/mysql-bin
log-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/3307ba K.sql
[root@localhost 3306]# mysql-uroot-pnick-s/data/3306/mysql.sock </opt/3307bak.sql
mysql> 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

Second, MySQL backup and recovery

1. Back up a single database

The most basic backup of a single database.

1> syntax: Mysqldump–u user name –p database name > BACKUP database name 2> backup nick_defailt database to view content. [Root@localhost ~]# mysqldump-uroot-p-b nick_defailt >/opt/mysql_nick_defailt.bak Enter 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 ' is not null, ' age ' tinyint (2) is not null DEFAULT ' 0 ', ' dept '
varchar () default NULL) Engine=myisam default charset=latin1;
LOCK TABLES ' Oldsuo ' WRITE;
INSERT into ' Oldsuo ' VALUES (2, ' ç´¢å® ', 0,null), (3, ' Ç´¢å°¼ ', 0,null), (4, ' åº åº ', 0,null);
UNLOCK TABLES;
DROP TABLE IF EXISTS ' student ';
 CREATE TABLE ' student ' (' QQ ' varchar () DEFAULT null, ' ID ' int (4) NOT null auto_increment, ' name ' char ' is not NULL, ' Suo ' int (4) default NULL, ' Age ' tinyint (2) is not null default ' 0 ', ' dept ' varchar (+) default null, ' Sex ' char (4) Defau
LT 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, ' Kangknag ', Null,0,null,null), (null,4, ' Kangkang ', Null,0,null,null, (null,5, ' Oldsuo ', Null,0,null,null), (null,6, ' Kangknag ', Null,0,null,null), (NULL,7, '
Kangkang ', null,0,null,null);
UNLOCK TABLES; 3> Enable compressed backup database [root@localhost~] #mysqldump-uroot-p-B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz Enter Password: [root@localhost ~]# ll/opt/total dosage 28-rw-r--r--. 1 root root 2109 October 16:36 data_bak.sq2-rw-r--r--. 1 root root 2109 October 16:36 data_bak.sql-rw-r--r--. 1 root root 1002 October 11:55 mysql_nick_defailt.bak-rw-r--r--. 1 root root 1002 October 11:56 mysql_nick_defailt.bak.gz-rw-r--r--. 1 root root 3201 October 11:46 mysql_nick_defailt_b.bak drwxr-xr-x. 2 root 4096 November 2013 Rh-rw-r--r--. 1 root 1396 October 16:11 student_bak.sql 4> restore nick_defailt database [root@localhost ~]# mysql-uroot-p Nick_defailt < /opt/mysql_nick_defailt.bak Enter Password: #加-B recovery method [Root@localhost ~]# mysql-uroot-p </opt/mysql_nick_defailt_b.bak Enter password:5> Summary 1, backup with-b parameter.
Add use DB, and create database information. 2, with gzip to backup data compression.

2. Back up multiple databases

What about backing up multiple databases?

#多个数据库名中间加空格
[root@localhost ~]# mysqldump-uroot-p-B nick_defailt Oldsuo oldsuo_1|gzip>/opt/mul.sql.gz
Enter Password:

3. Backup single and multiple tables

What if you back up a single and multiple 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.bak
Enter 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    
Enter >/opt/mysql_nick_defailt.bak:

4, the mysqldump parameter

Key parameters of Mysqldump

-b specifies more than one library, adding build and use statements.
--compact Remove annotations, suitable for debugging output, production not.
-A backs up all libraries.
-F refreshes the Binlog log.
--master-data increases the Binlog log file name and the corresponding location point.
-x,--lock-all-tables
-L,--locktables
-D only back up table structure
-T only back up data
--single-transaction suitable for INNODB transaction database backup.

5. Incremental recovery

Importantly, the production environment is generally incremental backup and recovery; the so-called increment, is to continue to add data on the basis of the original data, do not have to add each time, save time and effort.

A: Incremental Recovery Prerequisites:
1. Open MySQL database log-bin parameter record Binlog log.
[root@localhost 3306]# grep log-bin/data/3306/my.cnf
log-bin =/data/3306/mysql-bin
2. The existence of a database is fully prepared.
B: Production environment mysqldump backup command:
# to complete the database, (production environment also through the regular hours of the day to perform the task)
mysqldump-uroot-pnick-s/data/3306/mysql.sock- DEFAULT-CHARACTER-SET=GBK--single-transaction-f-B Nick |gzip >/server/backup/mysql_$ (Date +%F). sql.gz
InnoDB engine backup
mysqldump-u$myuser-p$mypass-s $MYSOCK-F--single-transaction-a-B |gzip > $DATA _file
# MyISAM Citation Engine backup
mysqldump-u$myuser-p$mypass-s $MYSOCK-F-a-b--lock-all-tables |gzip > $DATA _file
C: Recovery:
# through firewalls Prohibit applications such as Web writing data to the main library or locking tables. Let the main library temporarily stop the update before resuming.
# misoperation Delete Nick Library!
1. Check all preparation and Binlog log
[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. Immediately refresh 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/
#误操作log-bin, second to last
[Root@localhost backup]# mysqlbinlog-d Nick mysql-bin.000030 >bin.sql  #导出为. sql format.
[root@localhost backup]# vim bin.sql
Find statement drop database Nick Delete!!! (Incorrect operation statement)

3. Recovery

[Root@localhost backup]# mysql-uroot-pnick-s/data/3306/mysql.sock <mysql_2015-10-31.sql    #恢复之前的数据库全备
[Root@localhost backup]# mysql-uroot-pnick-s/data/3306/mysql.sock Nick < bin.sql
#恢复删除误操作语言的bin-log.
# take care of!!!

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, but also hope that a lot of support cloud Habitat community!