MySQL based Primary master backup

Turn on binary and relay logs on two servers

If the database ID of two servers starts the autogrow feature, to set one ID in the configuration file is odd, the other one has an even number, or one has an even ID, and the other one has an odd ID, because if this is not set, It is possible that users on both servers will have the same ID number when they write, so it is generally not recommended that the ID number of the database be enabled for autogrow, or generated with the ID generator.

First, environmental description:

ip	host name	database name	Connect user	password
192.168.47.179	m ARIADB Retail	< P style= "font-family: ' equal line '; Font-size:13px;color: #000000;" >server01	server01
192.168.47.178	m ARIADB Retail	< P style= "font-family: ' equal line '; Font-size:13px;color: #000000;" >server02	server02

Second, the construction steps

2.1 Connecting users that create data

MARIADB01 above creates the connection user Server01, and can only connect through 192.168.47.178;

Script:mariadb[none]> GRANT REPLICATION SLAVE on*.* to ' Server01 ' @ ' 192.168.47.178 ' identified by ' Server01 ';

MARIADB02 above creates the connection user Server02, and can only connect through 192.168.47.149;

Script:mariadb[none]> GRANT REPLICATION SLAVE on*.* to ' server02 ' @ ' 192.168.47.179 ' identified by ' Server02 ';

Mariadb[none]> select User,password,host from Mysql.user; ---See if an authorized user is used for master-slave replication

2.2 Modifying the MySQL parameter file

Modify the MARIADB01 parameter file, the main configuration file in MySQL defaults to/etc/my.cnf, modify/Add the following content

[MySQL]

Skip_name_resolve = On
Innodb_file_per_table = On
Max_connections = 20000
Log_bin = Bin-log---Turn on two-level logging
Relay_log = Relay-log---Turn on the trunk log
server_id = 1
Auto_increment_offset = 1---Indicates ID number starting from 1
Auto_increment_increment = 2---Indicates that 2 is a step, which is an odd number

After modification, restart MSYQL effective: servicemysqld restart

Modify the MARIADB02 parameter file, the main configuration file in MySQL defaults to/etc/my.cnf, modify/Add the following content

[MySQL]
Skip_name_resolve = On
Innodb_file_per_table = On
Max_connections = 20000
server_id = 2
Relay_log = Relay-log
Log_bin = Bin-log
Auto_increment_offset = 2
Auto_increment_increment = 2

Parameter description:

A, server_id database for data synchronization when used to identify which server the statement was originally written from, in the main master or master-slave construction, are required to fill;

B, auto_increment_increment: In database applications, we often use unique numbers to identify records. The Auto_increment property of the data column can be automatically generated in MySQL. To avoid duplication of code generated by two primary databases, you need to set this value

2.3 Copy one of the server's databases to another server

Because the environment is completely new, so the two environment is the same, and need to carry out the initialization of data work;

you can do this with the following steps, as required by the M ariadb0 1 To synchronize the source database, do the following:

2.3.1. Lock mariadb01 database to view status

Mariadb[none]> FLUSH TABLES with READ LOCK;

Query OK, 0 rows Affected (0.00 sec)

mariadb[none]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000008 | 107| | |

+------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Show the source database is in the 107 position of number 8th Binlog;

2.3.2. Backing up the database

[[email protected]ariadb01~]# mysqldump--all-databases--lock-tables--flush-logs >/tmp/retail.sql

[[email protected]ariadb01 ~]# Scp/tmp/retail.sql 192.168.47.178 :/tmp/

2.3.3. Pause the binary log at MYSQL02 for import operations;

mariadb[none]< SET @ @session. sql_log_bin=0;

[[Email protected] ~]# </tmp/retail.sql# import Retail database

mariadb[none]< SET @ @session. sql_log_bin=1;

Mariadb[none]> FLUSH TABLES with READ LOCK;

2.4 . Lock m ysql02 The database and view the status

Mysql> SHOW Masterstatus;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000009 | 107| | |

+------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Displays the backup file located in the 107 position of number 9th Binlog;

2.5 The communication connection of the main master;

A, in M ariadb0 1 the user and Binlog are confirmed by the above

mysql> change mastertomaster_host= ' 192.168.47.178 ', master_user= ' Server02 ', master_password= ' Server02 ', master_ Log_file= ' mysql-bin.000008 ', master_log_pos=107;

Mysql>start slave;

Mysql> Showslave Status\g

b, in the MARIADB02 above the user and Binlog confirmation

mysql> change mastertomaster_host= ' 192.168.47.179 ', master_user= ' Server01 ', master_password= ' Server01 ', master_ Log_file= ' mysql-bin.000009 ', master_log_pos=107;

Mysql>start slave;

Mysql> Showslave Status\g

These two main items:

Slave_io_running:yes

Slave_sql_running:yes

unlock m separately ariadb0 1 , Mariadb0 2 Database

Mariadb[none]> UNLOCK TABLES;

Test

On the mysql01 write on mysql02 there is a display

On the mysql02 write on mysql01 there is a display

Issues to be aware of when copying:

1, from the service set to "Read Only": In the boot from the server read_only, but only for non-super rights of users;

To prevent all users from being able to request a global read lock: Mysql>flush TABLES with read lock;

2, try to ensure that the replication of the transaction security

The master node enables the parameter: Sync_binlog=on, which means that the current node will be saved from the memory buffer to the binary log as soon as it is committed, to avoid copying from the server when the binary log does not have this commit operation the primary server is bad, So from the server I don't know if this thing should be submitted.

If you are using the InnoDB storage engine:

Innodb_flush_logs_at_trx_commit=on---Sync to the things log whenever things are committed

Innodb_support_xa=on let InnoDB support distributed things

3, from the server unexpectedly aborted when try to avoid automatically start the replication thread, if from the server when copying an event copy to half of the time from the server unexpectedly terminated, if restarted, the replication function will automatically open, because in the/var/lib/mysql/ The Master.info file records the information connected to the primary server, so the replication thread is started automatically when the MARIADB service is started, which can cause problems because things are copied to half before termination, and we don't know whether to commit them, in order to avoid this, we have to break the net off and see if there is anything that is copied to half. There are manual deletions, and then manually add Changemaster to point to the location of the primary server binary log at the time of an unexpected termination, or restart the replication function after restoring the restore

4. From node: setting parameters

Sync_master_info=on

Sync_relay_log_info=on

This article is from the "13147015" blog, please be sure to keep this source http://13157015.blog.51cto.com/13147015/1981101

MySQL is based on primary primary backup