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

Main points of principle of mysql Master-Slave replication

1. Asynchronous synchronization

2. Logical synchronization mode, multiple modes, which are executed by sql statement by default

3. The master library synchronizes the slave database by recording binlog, and binlog records the update statements of the database.

4. One IO thread in the master library, and one IO thread and one SQL thread in the slave library.

5. Master.info,relay-log,relay-info function of key files from the library

6. If you want to cascade slave libraries, you need to open the parameters bin-log and log-slave-updates.

Multi-instance demonstration of incremental recovery of mysql database

[root@opm02-test ~] # mysql-uroot-poldboy-S / data/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 8

Server version: 5.5.32-log Sourcedistribution

Mysql > show variableslike'% character_set%'; # # View mysql character set

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

| | character_sets_dir | / application/mysql-5.5.32/share/charsets/ |

+-+

8 rows in set (0.02 sec)

Mysql > create database oldboy; # # create an oldboy library

Query OK, 1 row affected (0.01sec)

Mysql > use oldboy

Database changed

Mysql > create table test (id int (4) notnull auto_increment, name char (20) notnull, primary key (id); # # create a test table

Query OK, 0 rows affected (0.11 sec)

Mysql > show create table test; # # View table creation statements

+- -- +

| | Table | Create Table |

+- -- +

| | test | CREATE TABLE `test` (

`id`int (4) NOT NULL AUTO_INCREMENT

`name` char (20) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+- -- +

1 row in set (0.01 sec)

Mysql > insert into test (id,name) values; # # insert data into test table

Query OK, 1 row affected (0.02 sec)

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

+-+ +

1 row in set (0.01 sec)

Mysql > insert into test (id,name) values (# # insert data oldboy11 in test table

Query OK, 1 row affected (0.01sec)

Mysql > insert into test (id,name) values (3dboy 12'); # # insert data oldboy12 into test table, and so on

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test; # # View the data in the table

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | oldboy11 |

| | 3 | oldboy12 |

| | 4 | oldboy13 |

| | 5 | oldboy14 |

| | 6 | oldboy15 |

+-+ +

6 rows in set (0.00 sec)

Mysql > quit

Bye

[root@opm02-test ~] # date-s' 2016-06-28'# # make a backup at 12:00

Tuesday, June 28, 2016, 00:00:00 CST

[root@opm02-test] # mysqldump-uroot-poldboy-S / data/3306/mysql.sock-F-B oldboy > / opt/quanbei.sql # # make a full backup

[root@opm02-test ~] # ll / opt/

Total dosage 524

-rw-r--r--. 1 root root 2087 June 28 00:02 quanbei.sql

-rw-r--r--. 1 root root 528847 May 26 01:40 rep.sql

[root@opm02-test ~] # mysql-uroot-poldboy-S / data/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 10

Server version: 5.5.32-log Sourcedistribution

Mysql > use oldboy

Database changed

Mysql > desc test

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (4) | NO | PRI | NULL | auto_increment |

| | name | char (20) | NO | | NULL |

+-+ +

2 rows in set (0.01sec)

Mysql > insert into test (name) values ('oldboy101'); # # insert 2 statements to make increments

Query OK, 1 row affected (0.02 sec)

Mysql > insert into test (name) values ('oldboy102'); # # insert statements for increments

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | oldboy11 |

| | 3 | oldboy12 |

| | 4 | oldboy13 |

| | 5 | oldboy14 |

| | 6 | oldboy15 |

| | 7 | oldboy101 |

| | 8 | oldboy102 |

+-+ +

8 rows in set (0.00 sec)

Mysql > show databases; # # View the oldboy library before deletion

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | oldboy |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > drop database oldboy; # # delete the oldboy library

Query OK, 1 row affected (0.03 sec)

Mysql > show databases; # check the oldboy library after deletion. It's gone.

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > quit

Bye

[root@opm02-test ~] # ll / opt/ # # View backup data catalog

Total dosage 524

-rw-r--r--. 1 root root 2087 June 28 00:02 quanbei.sql

-rw-r--r--. 1 root root 528847 May 26 01:40 rep.sql

[root@opm02-test ~] # cd / data/3306/ # # View the binlog log of the database

[root@opm02-test 3306] # ll

Total dosage 32

Drwxr-xr-x. 5 mysql mysql 4096 June 28 00:07 data

-rw-r--r--. 1 mysql mysql 1899 May 26 00:35 my.cnf

-rwxr-xr-x. 1 mysql mysql 1307 May 26 00:41 mysql

-rw-rw----. 1 mysql mysql 2290 June 28 00:02 mysql-bin.000001

-rw-rw----. 1 mysql mysql 656 June 28 00:07 mysql-bin.000002 # # binlog location

-rw-rw----. 1 mysql mysql 56 June 28 00:02 mysql-bin.index

-rw-rw----. 1 mysql mysql 6 May 26 00:57 mysqld.pid

-rw-r-. 1 mysql root 2669 May 26 01:39 mysql_oldboy3306.err

Srwxrwxrwx. 1 mysql mysql May 26 00:57 mysql.sock

[root@opm02-test 3306] # mysqladmin-uroot-poldboy-S/data/3306/mysql.sock flush-logs # # if the binlog location point is uncertain, refresh the binlog log with mysqladmin. The binlog location point is restored before the new binlog is generated, and whether it is operated at 12 am according to the time point.

[root@opm02-test 3306] # ll

Total dosage 36

Drwxr-xr-x. 5 mysql mysql 4096 June 28 00:07 data

-rw-r--r--. 1 mysql mysql 1899 May 26 00:35 my.cnf

-rwxr-xr-x. 1 mysql mysql 1307 May 26 00:41 mysql

-rw-rw----. 1 mysql mysql 2290 June 28 00:02 mysql-bin.000001

-rw-rw----. 1 mysql mysql 699 June 28 00:17 mysql-bin.000002

-rw-rw----. 1 mysql mysql 107 June 28 00:17 mysql-bin.000003 # # newly generated binlog log

-rw-rw----. 1 mysql mysql 84 June 28 00:17 mysql-bin.index

-rw-rw----. 1 mysql mysql 6 May 26 00:57 mysqld.pid

-rw-r-. 1 mysql root 2669 May 26 01:39 mysql_oldboy3306.err

Srwxrwxrwx. 1 mysql mysql May 26 00:57 mysql.sock

[root@opm02-test 3306] # cp mysql-bin.000002/opt/

[root@opm02-test 3306] # ll / opt/

Total dosage 528

-rw-r-. 1 root root 699 June 28 00:18 mysql-bin.000002

-rw-r--r--. 1 root root 2087 June 28 00:02 quanbei.sql

-rw-r--r--. 1 root root 528847 May 26 01:40 rep.sql

[root@opm02-test 3306] # mysqlbinlog-d oldboy / opt/mysql-bin.000002## View binlog log content

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET@@session.max_insert_delayed_threads=0*/

/ *! 50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

[root@opm02-test3306] # cd / opt/

[root@opm02-testopt] # mysqlbinlog-d oldboy mysql-bin.000002 > bin.sql # # put binlog under bin.sql

[root@opm02-testopt] # vi bin.sql # # vi edits the statement that deletes drop data, and saves and exits

[root@opm02-test opt] # mysql-uroot-poldboy-S / data/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 13

Server version: 5.5.32-log Sourcedistribution

Mysql > show variables like 'log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > quit

Bye

[root@opm02-test opt] # mysql-uroot-poldboy-S / data/3306/mysql.sock select * from oldboy.test

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | oldboy11 |

| | 3 | oldboy12 |

| | 4 | oldboy13 |

| | 5 | oldboy14 |

| | 6 | oldboy15 |

| | 7 | oldboy101 |

| | 8 | oldboy102 |

+-+ +

8 rows in set (0.00 sec)

Mysql >

Incremental recovery summary:

1. Misoperation caused by human sql

2. Be complete and incremental

3. It is recommended to stop updating when restoring.

4. Restore the full amount, then delete the problematic sql statements in the incremental log and restore them to the database

Summarize the database incremental recovery:

1. Stop a slave library, refresh binlog in the master library, and restore mysql-bin-000002 to bin.sql (vi bin.sql).

Remove deleted statements, / drop search deleted statements, and kill them in vi

2. Restore the full quanbei.sql and incremental bin.sql before 10:00 to the slave database

3. How much data is lost? Refresh the data mysql-bin.000003 after binlog at 10:10

4. Stop the master library, quickly parse mysql-bin.000003 to sql, and restore to the slave library.

5. Switch to providing services from the library

The core idea of incremental recovery:

1. Process system control to prevent problems. If you do not do so, you will face the problem of service and data loss.

2. Solve the problem by delaying backup, or monitoring, blacklist or whitelist mechanism

3. Tolerance of business requirements, choose to stop the library or lock the table or tolerate the loss of some data

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.