MySQL Master-slave configuration

In the actual enterprise application environment, the single MySQL database is insufficient to meet the future business requirements. For example, if a server fails and there is no backup server to provide the service, the business will have to stop. In this case, let's learn MySQL master-slave replication.

The benefits of using MySQL master-slave replication are:

1, the use of master-slave server This architecture, stability can be improved. If the primary server fails, we can use the slave server to provide the service.

2, processing the user's request separately on the master-slave server, can improve the data processing efficiency.

3. Copy the data on the primary server to the slave server to protect the data from accidental loss.

Environment Description:

The new enterprise wants to build a MySQL database that is based on replication.

Primary server (mysql-master): IP address: 192.168.124.128,mysql installed, no user data.

From server (mysql-slave): IP address: 192.168.124.129,mysql installed, no user data.

The master-slave server can provide the service normally.

Synchronizing the entire database

The master-slave replication configuration is as follows:

One. To operate on the primary server:

1), ensure that the/ETC/MY.CNF has the following parameters, no need to manually add, and restart the MySQL service.

[Mysqld]

Log-bin=mysql-bin starting a binary file

Server-id=1 Server ID

2), log in to MySQL, add an AAA account to MySQL, and authorize it to the slave server.

[[email protected] ~]# mysql-uroot–p123456 login mysql (default no password)

mysql> grant replication Slave on * * to ' aaa ' @ ' 192.168.124.129 ' identified by ' 1234 '; Create AAA users and authorize 192.168.124.129 to use.

3), query the state of the main database, and note the value of file and position, which is used when configuring from the server later.

Mysql> Show master status;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Two. To operate from the server:

1), ensure that there are log-bin=mysql-bin and server-id=1 parameters in the/ETC/MY.CNF, and change the server-id=1 to server-id=2. The changes are as follows:

[Mysqld]

Log-bin=mysql-bin starting a binary file

server-id=2 Server ID

2), restart the MySQL service.

[[Email protected] ~] #service mysqld Restart

3), log in to MySQL, execute the following statement

[Email protected] ~]# mysql-uroot–p123456

mysql> Change Master to master_host= ' 192.168.124.128 ', master_user= ' aaa ', master_password= ' 1234 ', master_log_file= ' mysql-bin.000001 ', master_log_pos=258;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

4), start slave synchronization.

mysql> start slave;

5), check the master-slave synchronization, if you see slave_io_running and slave_sql_running are yes, the master-slave replication connection is normal.

Mysql> Show Slave Status\g

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Verify that the configuration is correct and that MySQL master/slave can replicate normally.

Create a new library on the primary database, and write a table and some data in the library.

[Email protected] ~]# mysql-uroot–p123456

mysql> CREATE DATABASE AAA;

Mysql> use AAA;

Mysql> CREATE TABLE User (id int (5), name char (10));

mysql> INSERT into user values (00001, ' Zhangsan ');

Verify from the database that it is copied to the data normally.

[Email protected] ~]# mysql-uroot–p123456

mysql> show databases;

Mysql>use AAA;

Mysql>select * from Aaa.user;

As a result, we can see that MySQL master-slave replication is already working, and the data we write in the main database has been copied to our database.

Synchronizing a single library

The master-slave replication configuration is as follows:

To operate on the primary database:

1), ensure that the/ETC/MY.CNF has the following parameters, no need to manually add, and restart the MySQL service.

[Mysqld]

Log-bin=mysql-bin starting a binary file

Server-id=1 Server ID

Binlog-do-db=test #指定需要日志的数据库

[[Email protected] ~] #service mysqld Restart

2), login MySQL, add a user synchronization in MySQL account Haha, and authorized to from the server that is slave machine has file permissions, only give slave machine has file permissions not yet, but also to it replication slave permissions can be.

[Email protected] ~]# mysql-uroot–p123456

Mysql>grant file on * * to ' haha ' @ ' 192.168.124.129 ' identified by ' 1234 ';

Mysql>grant replication Slave on * * to ' haha ' @ ' 192.168.124.129 ' identified by ' 1234 ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

3), use show Master status/g command to see the log situation. Normal is:

4), the lock table operation, do not let the data write action, so do things to prevent the original data from the database and the primary database is inconsistent with the original data.

Mysql> flush tables with read lock;

5), use the mysqldump command to guide the library you just queried.

[[Email protected] ~] #mysqldump –uroot–p123456 AAA > Aaa.sql

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

6), transfer the library file from the database to the/tmp directory.

[[Email protected] ~] #scp-R aaa.sql [email protected]:/tmp

You will need to enter a verification password in the middle. See

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

7), enter MySQL, perform table unlock operation.

mysql> unlock tables;

The operation on the primary database server is over.

On the operation from the database:

1), modify the configuration file/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin starting a binary file

server-id=2 Server ID
master-host=192.168.124.128
Master-user=haha #同步用户帐号
master-password=1234
master-port=3306
Master-connect-retry=60 #预设重试间隔60秒
REPLICATE-DO-DB=AAA #告诉slave只做backup数据库的更新

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

2), restart the MySQL service.

[Email protected] ~]# service mysqld restart

3), log in to the database and create a library called AAA.

mysql> CREATE DATABASE AAA;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

4), the data files will be transmitted to the corresponding database.

[Email protected] ~]# mysql-uroot–p123456 AAA < Aaa.sql

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

5), check the master-slave synchronization, if you see slave_io_running and slave_sql_running are yes, the master-slave replication connection is normal.

Mysql> Show Slave Status\g

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Verify that the master/slave configuration is normal:

To insert data on the primary database:

mysql> show databases;

Mysql> Show tables;

Mysql> use AAA;

Mysql> select * from Aaa.user;

mysql> INSERT INTO user values (4, ' Ergou ');

mysql> INSERT INTO user values (5, ' Sanwazi ');

To see if the synchronization is normal from the database:

mysql> show databases;

Mysql> use AAA;

Mysql> select * from Aaa.user;

PS is both the master and do from the MY.CNF must write a line

Log-slave-updates

MySQL Master-slave configuration