CentOS 6.5 Set up the MySQL master-slave synchronization process record

The MySQL master and slave function was set up on CentOS 6.5, and recorded.

Server 1 (primary)
ip:192.168.1.201
System version: CentOS 6.5
MySQL version: MySQL 5.5

Server 2 (from)
ip:192.168.1.202
System version: CentOS 6.5
MySQL version: MySQL 5.5

The system version of both servers is consistent with the MySQL version, which is also the official recommended practice. Before you start setting up, it's best to make sure that the main library is consistent with the library.

1, the main library and from the library to create synchronization users

mysql> grant replication Slave, replication client on *.* to repl@ ' 192.168.1.% ' identified by ' password ';

Here the master-slave synchronization is limited to the local area network.

2, the main library configuration

Edit the MySQL configuration file/etc/my.cnf, set the following configuration:

Log-bin=mysql-bin
binlog_format=mixed
server-id = 1

Binlog_format here is the blending mode, which is also the default value of MY.CNF in the MySQL 5.5 installation package.

3, from the library configuration

Edit the MySQL configuration file/etc/my.cnf, set the following configuration:

Log-bin=mysql-bin
binlog_format=mixed
server-id = 2
relay_log =/var/lib/mysql/mysql-relay-bin
Log _slave_updates = 1
read_only = 1

4, starting from the library

mysql> Change Master to master_host= ' 192.168.1.201 ',
master_user= ' Repl ',
master_password=
' 100301 ', Master_log_file= ' mysql-bin.000001 ',
master_log_pos=0;

Turn on Sync

mysql> start slave;

View status

Mysql> Show Slave STATUSG

View replication status from a library

If the configuration is all correct, then the master and slave will begin to work. If the master and subordinate do not work properly, you can use the command of MySQL with the debugging.

Error debugging

Use show slave status to view Last_io_error state information:

Mysql> Show Slave STATUSG

Master-Slave Settings connection error

The above situation indicates that the main library cannot be connected properly. Because the master library cannot be connected, Slave_io_state will always display connecting to master. You can debug from the library by connecting to the main library from the MySQL client.

$ mysql-h Main Library-U repl-p

View worker Processes

View process on Main library:

Mysql> Show Processlistg

View Main Library Threads

From state information you can see that the main library has sent out all the Binlog

To view a process from the library:

Mysql> Show Processlistg

View from Library threads

One is an I/O thread and one is an SQL thread.