MySQL master-Slave Library setup (original)

MYSQL主从库搭建(原创)

First, Tankiku configuration
1.1 Environmental Description
This section describes the environment as a master library, two from a library, as follows.
1.1.1Linux version
Red Hat Enterprise Linux Server release 6.4 (Santiago)
1.1.2 MySQL Version
5.1.73
1.1.3 Master server IP 192.168.212.134, slaver server IP 192.168.212.142, slaver server IP 192.168.212.143

1.2 Master server Configuration
1.2.1 Create a Sync account
mysql> grant replication Slave on . to ' rep ' @ ' 192.168.212.% ' identified by ' rep ';
mysql> flush Privileges;
Description: represents all tables for all databases, or you can specify that all tables of a database be synchronized, such as testdb.*
192.168.212.% representative can connect to the master database for the 192.168.212 network segment
1.2.2 Opening the Binlog function of the master database
Edit/etc/my.cng
Add the Bin-log parameter to the Mysqld module. such as Bin-log=master
Restart database
#service mysqld Restart
1.2.3 Backing up the database
? To maintain consistency, check the Binlog.
Mysql>show Master status;
The current statement node is in the mysqlbin.000002 position at 106, which restores the starting position in the Slaver database
Mysql>show master logs;

? Backup Full Library
To keep the data consistent, for non-INNODB library tables that require a lock library for backup, you can mysql>flush table with read lock via the MySQL command, lock the database (unlock tables; unlock), The time of this command lock table is controlled by the system parameters Wait_time and Interactive_timeout, and the default time is 28,800 seconds. If the command execution window is closed, the lock table also fails.
You can also add the parameter-X lock table in mysqldump, such as the following command # mysqldump-uroot-b-a-x--events–master-data=2|gzip >/root/mysql_all.gz
For InnoDB storage engine tables, you can add parameters in mysqldump--single-transaction snapshots at backup time to keep the data consistent, such as the following command # Mysqldump-uroot-b-A--single-transaction-- Events--master-data=2|gzip >/root/mysql_all.gz
Transfer the backup file to the Slaver server as follows:
scp/root/mysql_all.gz [Email protected]:/home/mysql/

1.3 slaver Server Configuration
This section is configured for 192.168.212.142 only, and the 192.168.212.143 configuration is the same.
1.3.1 Import the backup data into the database as follows:
$gzip –dv/root/mysql_all.gz
$mysql –uroot </root/mysql_all
1.3.2 Setting up recovery points
Mysql>change Master to
Master_host= ' 192.168.212.134 ',
master_port=3306,
Master_user= ' rep ',
Master_password= ' rep ',
Master_log_file= ' mysqlbin.000002 ',
master_log_pos=106;
Description
Fill in the backup file with the Change Master command Master_log_file,master_log_pos value to the corresponding location of the above command. If you set--master-data=1 at backup (2 will annotate the incremental location information), the incremental location information is written to the backup file, and the backup file is imported from the database to import the location information together, so that you no longer need to write when you perform change master from the library Master_log_ File and Master_log_pos.
1.3.3 Start synchronization
Mysql>start slave;
1.3.4 Check master and slave status
Mysql>show slave status\g;
Mysql> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.212.134
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysqlbin.000004
read_master_log_pos:106
relay_log_file:mysqld-relay-bin.000004
relay_log_pos:250
relay_master_log_file:mysqlbin.000004
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:106
relay_log_space:550
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
1 row in Set (0.00 sec)
ERROR:
No query specified
Description: If both slave_io_running and slave_sql_running are yes to start the synchronization, Seconds_behind_master indicates the time (in seconds) that the library is falling behind the main library.

Second, multi-master library configuration
The multi-master library mode contains more than one main library, and the Inter-library database synchronizes with each other. Master1 and Master2 represent two main libraries respectively.
2.1 Parameter Configuration
Mater1:
Auto_increment_offset=1 #解决主键自增变量冲突. Self-increment initial position, this example is 1
auto_increment_increment=2 #解决主键自增变量冲突. The self-increment interval is 2, for example 1, 3, 5
Log_slave_updates
Mater2:
auto_increment_offset=2 #解决主键自增变量冲突. Self-increment initial position, this example is 2
auto_increment_increment=2 #解决主键自增变量冲突. The self-increment interval is 2, for example 2, 4, 6
Log_slave_updates

2.2 Master1 Data Backup, import the MATER2 database
Refer to the Backup and import section of Tankiku.

2.3 Master1 Start Sync
Mysql> Change Master to
Master_host= ' 192.168.212.141 ',
master_port=3306,
Master_user= ' rep ',
Master_password= ' rep ';
Mysql>start slave;

2.4 Master2 Start Sync
Mysql> Change Master to
Master_host= ' 192.168.212.141 ',
master_port=3306,
Master_user= ' rep ',
Master_password= ' rep ';
Mysql>start slave;

Third, automatic deletion of Binlog log
You can configure Binlog log retention days through the system parameters expire_logs_days. The default value for this parameter is 0,
For example, expire_logs_days=10, which means retention for 10 days.

Iv. Reading and writing separation
To prevent the application from using DML operations from the library and the parameter master never agrees, you can add the Read-only parameter from the library so that read-only from the library (super, or all privileges permission users are not limited).

V. Switch from library to new main library
5.1 Choose to synchronize the latest from the library to the new Main library.
5.1.1 View each slave library if the following red box shows the same synchronization.

5.1.2 View two master.info files from the library, compare different from the red part of the library to determine whether the latest, select Logbin file and POS largest is the latest, this example is logbinfile:000006,pos:106.
#cat Master.info
15
mysqlbin.000006
106
192.168.212.134
Rep
Rep
3306
60
0
5.2 Import all Relaylog content from the library into the library.
Execute the Stop slave io_thread;show processlist\g from the library, until you see the have read all relay log, indicating that the import is complete from the library.
mysql> stop slave Io_thread;
Mysql> Show Processlist\g

5.3 New Main Library operations

          新主库的检查

? In the data directory of the new host, delete the Master.info,relay-log.info two files.
? Confirm that the Mysql.user table meets the application and other connection requirements from the library.
? Verify that the read-only and log_slave_updates system parameters have been removed.
? Confirm that the Binlog feature is turned on.
? Check to see if there are any binlog that are not synced to the new main library, and do not sync to the new Binlog from the Library by Mysqlbinlog tool to generate a readable command document and import the new Main library.

In the synchronization of the latest from the library to execute the following statement, from the library to the new Main library, the statement is as follows.
Mysql>stop slave; --Stop slave service
Mysql>reset master; --Binlog initialization of the main library
#service mysqld Restart Restart the database
5.4 Other operations from the library
Mysql>stop slave;
Mysql>change Master to Master host= ' 192.168.212.142 '; --point to the new Main library
Mysql>start slave;
Mysql>show slave status\g;

MySQL master-Slave Library setup (original)