MySQL Master-slave replication

1, copy of the introduction

MySQL supports one-way, asynchronous replication, during which one server acts as the primary server, and one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

One-way replication facilitates robustness, speed, and system management:

· The master server/slave server setting adds robustness. When there is a problem with the primary server, you can switch to the server as a share.

· Better customer response times can be achieved by slicing the load of processing customer queries between the primary server and the slave server. Select queries can be sent to the slave server to reduce the query processing load on the primary server. However, the statements that modify the data should still be sent to the master server so that the master server and the server are synchronized. If the non-update query is primary, the load balancing policy is effective, but is typically an update query.

· Another benefit of using replication is that you can use one to perform backups from the server without interfering with the primary server. The master server can continue to process updates during the backup process.

MySQL provides synchronization of the database, which is very helpful for us to realize database redundancy, backup, recovery, load balancing and so on.

Two-way replication is based on one-way replication by establishing a one-way replication from slave to master, it is necessary to write the corresponding update policy, otherwise it is very easy to have data inconsistency problem.

2. Environment

Using the Radhat 5.1 os mysql5.1.50 version

Master computer name: ltest IP Address: 172.31.70.51

Slave Computer name: Erpdemo IP Address: 172.31.70.95

Note: The source installation package can only be installed above Redhat5, and there will be a problem compiling on REDHAT4.

3, MySQL one-way replication

Note that the MySQL database version, two database version to be the same, or slave than the master version of the high! The version used here is consistent.

1) Install the MySQL software on Ltest, Erpdemo, and install it through the source code.

The MySQL software can be downloaded on [Url]http://www.mysql.com[/url] and uploaded to the server via FTP or other software:

1) Add users and groups running MySQL to the Linux system
[[email protected] ~]# Groupadd MySQL
[[email protected] ~]# useradd-g MySQL MySQL

2) unzip the source package
[Email protected] ~]# TAR-ZXVF mysql-5.1.50.tar.gz
[Email protected] ~]# CD mysql-5.1.50

3) configuration Compilation

Configuring the installation directory for MySQL

[[email protected] mysql-5.1.50]#./configure--prefix=/usr/local/mysql) Compile and install
[[email protected] mysql-5.1.50] #make #编译
[[email protected] mysql-5.1.50] #make Install

5) load the original authorization to the database
[Email protected] mysql-5.1.50]#./scripts/mysql_install_db

6) Copy the MySQL configuration file to/etc directory
[email protected] mysql-5.1.50]# CP support-files/my-medium.cnf/etc/my.cnf
7) Copy the MySQL startup script to the resource directory
[[email protected] mysql-5.1.50] #cp./support-files/mysql.server/etc/rc.d/init.d/mysqld

8) Add MySQL service to start the MySQL service automatically when the system starts

[[email protected] mysql-5.1.50] #chmod +x/etc/rc.d/init.d/mysqld
[[email protected] mysql-5.1.50] #chkconfig--level 235 mysqld on
9) Change Directory Owner
[[email protected] mysql-5.1.50] #chown-R mysql.mysql/var/lib/mysql

10) Setting Environment variables
Add a line in/etc/profile, you don't have to enter a long path when you run MySQL
Export path= $PATH:/usr/local/mysql/bin

11) Start the MySQL service
[[email protected] mysql-5.1.50] #service mysqld start
Here the MySQL software will press to complete, Erpdemo on the MySQL installed on the line above.
B. Configure the MySQL configuration file
1) Enter the MySQL command line to add synchronization private permissions for slave users
Enter the password, go to the MySQL command line, the general just installed no password.
[[email protected] ~]# MySQL
Welcome to the MySQL Monitor. Commands End With; Or/g.
Your MySQL Connection ID is 3 to server version:5.1.50
Type ' help; ' or '/h ' for help. Type '/C ' to clear the buffer.
Mysql>

mysql> GRANT REPLICATION slave,replication client,reload,super on * * to ' REPLICATION ' @ ' 172.31.70.95 ' ientified by ' 12 3456 ';
#给与从服务器用户replication的同步权限
Mysql> Grant All privileges on * * to [E- Mail protected]'% ' identified by ' 123456 ';

#如果需要的话添加管理用户, test the synchronization situation with the MySQL client
Mysql>flush privileges;
#刷新权限 to make the settings effective

2) Configuring the Ltest/etc/my.cnf configuration file

Create a directory of update logs and give MySQL user permissions
[Email protected] ~]# Mkdir/var/log/mysql
[Email protected] ~]# chown-r mysql.mysql/var/log/mysql

Modify the following in the configuration file, if not added:

Log-bin=mysql-bin	Start the binary log system
Binlog-do-db=test	The name of the database in which the binary needs to be synchronized
Server-id = 1	The native database ID is marked primarily, and this section should also have a server-id=master_id option, where master_id must be a positive integer value between 1 and 232–1
Log-bin=/var/log/mysql/updatelog	#设定生成log文件名, the path here does not have the MySQL directory to manually create and give it the permissions of the MySQL user.
Binlog-ignore-db=mysql	# avoid syncing MySQL User configuration to avoid unnecessary hassle

3) Stop the database and copy the locally required synchronization database to the slave database

[[Email protected] ~] #service mysqld Stop #停止mysql的服务

[[Email protected] ~] #tar-cvf/root/db.tar/usr/local/mysql/test #备份主服务器需要同步的数据库

[[Email protected] ~] #scp/root/db.tar [email protected]:/usr/local/mysql #通过远程拷贝到从服务器上, through this copy you need to enter Erpdemo Root password.

[[Email protected] ~] #Service mysqld start #启动主服务器mysql服务

Second, synchronization slave from the server configuration

1) Configure the slave server/etc/my.cnf file

The following configuration is enabled:

Server-id = 2	From the server ID number, not the same as the primary ID, If you set multiple slave servers, each slave server must have a unique Server-id value that must be different from the primary server and the other from the server. Server-id values can be considered similar to IP addresses: These ID values uniquely identify each server instance in the replication server cluster.
Master-host = 172.31.70.51	Specify the home server IP address
Master-user = Replication	Specify a user name that can be synchronized on the primary server
Master-password = 123456	Password
Master-port = 3306	Ports Used for synchronization
Master-connect-retry=60	Breakpoint Reconnect Time
Replicate-ignore-db=mysql	Block synchronization of MySQL libraries to avoid trouble
Replicate-do-db=test	Synchronizing database names

2) Mount the primary server database:

[[Email protected] ~] #cd/var/lib/mysql #进入数据库库文件主目录

[[Email protected] ~] #tar-xvf Db.tar #解压缩

[[Email protected] ~] #service mysqld start #启动从数据库服务

Third, query configuration

On Ltest, go to MySQL command line

Use the following command to view

[email protected] ~]# MySQL #进入mysql命令行

Mysql> Show master status; #显示 (different host results are different)

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

| updatelog.000028 | 313361 |DB1 | MySQL |

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

(synchronization can be taken before synchronizing if the master-slave data is suspected: The above cold backup remote Copy method or the breakpoint line synchronization method from the server)

Under the Execute MySQL command from the server:

mysql> slave stop; #先停止slave服务

mysql> change MASTER to master_log_file= ' updatelog.000028 ', master_log_pos=313361;

#根据上面主服务器的show the results of the master status, the binary database records from the server are returned to achieve the effect of synchronization

Mysql>slave start; #启动从服务器同步服务
Mysql> show Slave status/g;

With show slave status/g; take a look at the synchronization from the server
Slave_io_running:yes
Slave_sql_running:yes

If yes, that means it's already in sync.

Using MySQL's client to test is much more convenient than on the command line.

For more details and parameter settings, please refer to the MySQL 5.0 manual manual.

4. mysql Bidirectional synchronization configuration

A, first modify the original slave server configuration

1) Configure the original slave server/etc/my.cnf file (red Word for add content)

Server-id = 2	From the server ID number, not the same as the primary ID
Master-host = 172.31.70.51	Specify the home server IP address
Master-user = Replication	Develop a user name that can be synchronized on the primary server
Master-password = 123	Password
Master-port = 3306	Ports Used for synchronization
Master-connect-retry=60	Breakpoint Reconnect Time
Replicate-ignore-db=mysql	Masking the synchronization of MySQL libraries
Replicate-do-db=db1	Synchronizing database names
Log-bin=/var/log/mysql/updatelog	Set Generate log file name
Binlog-do-db=db1	Setting the synchronization database name
Binlog-ignore-db=mysql	Avoid syncing MySQL User configuration to avoid unnecessary hassles

2) Restart the MySQL service and create a sync dedicated account

Enter the password, go to the MySQL command line, the general just installed no password.

[[email protected] ~]# MySQL
Welcome to the MySQL Monitor. Commands End With; Or/g.
Your MySQL Connection ID is 3 to server version:5.1.50
Type ' help; ' or '/h ' for help. Type '/C ' to clear the buffer.
Mysql>

mysql> GRANT REPLICATION slave,replication client,reload,super on * * to ' REPLICATION ' @ ' 192.168.1.2 ' identified by ' 12 3456 ';
#给与从服务器用户replication的同步权限
Mysql> Grant All privileges on * * to [E- Mail protected]'% ' identified by ' 123456 ';

#如果需要的话添加管理用户, test the synchronization situation with the MySQL client
Mysql>flush privileges;
#刷新权限 to make the settings effective

b, modify the original Master master server MY.CNF, add the following (red for the add part)

Log-bin=mysql-bin	Start the binary log system
Binlog-do-db=db1	The name of the database in which the binary needs to be synchronized
Server-id = 1	Native database ID is indicated as the primary
Log-bin=/var/log/mysql/updatelog	#设定生成log文件名
Binlog-ignore-db=mysql	# avoid syncing MySQL User configuration to avoid unnecessary hassle
Master-host = 172.31.70.95	Set up synchronization updates from the original slave database
Master-user = Repl	Update user
Master-password = 123	Password
Master-port = 3306	Port
Replicate-do-db=test	Libraries that need to be updated

Start the MySQL service
[[Email protected] ~] #service mysqld Restart

Under the Erpdemo server execution mysql command:
Mysql> Show master status;

See if there is any information as the primary server

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

| updatelog.000028 | 313361 |test | MySQL |

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

Under the ltest server execution mysql command:

[[Email protected] ~] #mysql #进入mysql命令行

mysql> slave stop; #先停止slave服务

mysql> change MASTER to master_host= ' 192.168.1.2 ', master_user= ' replication ', master_password= ' 123456 ', master_ Port=3306master_log_file= ' updatelog.000028 ', master_log_pos=313361;

#根据上面主服务器的show the results of the master status, the binary database records from the server are returned to achieve the effect of synchronization

mysql> slave start; #启动从服务器同步服务

C, testing

1) Enter the MySQL command line on the ltest server
[[Email protected] ~] #mysql
Mysql>show SLAVE status/
Slave_io_running:yes
Slave_sql_running:yes

Here slave_io_running, slave_sql_running should be yes, indicating that the i/o,slave_sql thread from the library is turned on correctly.

Indicates that the database is synchronizing.

2) Enter the MySQL command line on the Erpdemo server and show slave status;

[[Email protected] ~] #mysql
Mysql>show SLAVE status/
Slave_io_running:yes
Slave_sql_running:yes
Here slave_io_running, slave_sql_running should be yes, indicating that the i/o,slave_sql thread from the library is turned on correctly. Indicates that the database is synchronizing.

3) Here I found a MySQL client. Using the Admin user login database built on MySQL, you can write the value directly in the table and go to another database to see if it can be refreshed, where the data is written on the database.

MySQL Master-slave replication