MySQL Master-slave configuration

MySQL this thing, is a relatively easy to use database, but to be proficient, it takes a lot of effort.

Here are some MySQL simple master and slave configuration, write casually, have time to change, slowly progress it.

At present, the company is using 1 solid machine as a MySQL database, the previous period of time to do a configuration from the library, the current is only a simple master-slave synchronization, the next master and standby switching/data recovery is still in the study.

1. Theoretical basis

In the actual production environment, by a single MySQL as a separate database in the process of work, very easy due to a short period of frequent read and write operations, resulting in database load, traffic increase, resulting in server memory consumption is too high, and then the page card is dead, the system can not function properly. This is completely impossible to meet the actual needs, whether in terms of security, high availability and high concurrency, and many other aspects.

2. Preparatory work

2 physical machines as master and slave.

The MySQL installation version on the main library ip:192.168.0.111 Main Library is: 5.5.25

From the library ip:192.168.0.121 from the library MySQL installation version is: 5.5.43

3. Start

Main Library my.cnf configuration file:

Enable Log_bin, set the corresponding path such as log_bin=/var/lib/mysql/mysql-bin(MySQL binary log, record the operation of the database)

Server-id = 1

#Binlog-do-db=xxx (Configure the library to be copied)

#Binlog-ignore-db=xxx (Configure a library that you do not copy)

Restart the main library service mysqld restart

MY.CNF configuration file from library:

Enable Logs-bin

Server-id = 121

Skip-slave-start

replicate-ignore-db = mysql out of sync database MySQL

replicate-ignore-db = Test out of sync database test

replicate-ignore-db = information_schema out of sync database information_schema

Restart from library service mysqld restart

Create a new user from the library and give it permission to query the main library from the library Ip:user on the main library

mysql>grant REPLICATION SLAVE on * * to ' slave121 ' @ ' 192.168.0.121 ' identified by ' Slave121_password ';

Backing up the main library data, from the library

A. Backing up the main library data

Method 1:

mysqldump-uroot-p--single-transaction--master-data=2--all-databases--default-character-set= "UTF8" >/var/ Mysqlbk/111all.sql 2>/var/mysqlbk/111all.log Pack 0.111 data from the database to/var/mysqlbk/ 111all.sql, packing records in 111all.log Method 2: backing up the specified database, filtering test MySQL information_schema

Mysqldump-uroot-p--single-transaction--master-data=2--default-character-set= "UTF8"--databases xxx_a xxx_b Xxx_c ( Specify a packaged database) >/var/mysqlbk/111all.sql 2>/var/mysqlbk/111all.log

B. Restore data If you use Method 1, to exclude MySQL test information_schema

Method 1 corresponds to the restore:

Mysql-utest-p--one-database xxx_a Xxx_b xxx_c </tmp/111all.sql--default-character-set=utf8-h 127.0.0.1

Method 2 corresponds to the restore:

Mysql-utest-p </tmp/111all.sql--default-character-set=utf8-h 127.0.0.1

Enables synchronization of Log_bin from the library to read the main library

In the packaged 111all.sql file, look for the file and position values of binlog, such as mysql-bin.000039 261

On the run from the library:

Mysql> Change Master to
Master_host= ' 192.168.0.111 ',

Master_user= ' slave121 ',

Master_password= ' Slave121_password ',

Master_log_file= ' mysql-bin.000039 ',

master_log_pos=261;

Mysql>slave start;

mysql>show slave status;

1. Row *******************
Slave_io_state:
master_host:192.168.0.111
Master_user:root
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000039
read_master_log_pos:415
relay_log_file:localhost-relay-bin.000008
relay_log_pos:561
relay_master_log_file:mysql-bin.000005
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
............... To omit a number of ......
Master_server_id:1
1 row in Set (0.01 sec)
==============================================

The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

At this point, MySQL's master-slave synchronization is implemented.

Validation: my.cnf file definition binlog_format=mixed (row,statement not recommended) in the main library, observe the effects of stored procedures, views, etc. on data consistency.

MySQL Master-slave configuration