MySQL master-slave database replication

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while 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.

types of replication supported by MySQL:

(1): statement-based replication: SQL statements executed on the primary server execute the same statement from the server. MySQL uses statement-based replication by default and is more efficient.
Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
(2): Row-based replication: Copy the changed content past, rather than executing the command from the server again. Support Starting from mysql5.0
(3): Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

replication-Resolved issues

MySQL replication technology has some of the following features:
(1) Data distribution (distribution)
(2) Load balancing (load Balancing)
(3) Backup (Backups)
(4) High-availability and fault-tolerant lines of availability and failover

How replication works

As a whole, there are 3 steps to replication:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the event in the trunk log and change the data to reflect its own.

Replication process:

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/8B/E2/wKiom1hbdW7iiQ1AAABUrjWR9Ok798.jpg "title=" 1234. JPG "alt=" wkiom1hbdw7iiq1aaaburjwr9ok798.jpg "/>

Specific implementation:

1, the master-slave server for the following actions :
1.1. Consistent version
1.2. Initialize the table and start MySQL in the background
1.3, change the root password

2. Modify master server Master:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
server-id=222//[must be] server unique ID, default is 1, usually take IP last paragraph

3. Modify the slave from the server:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[not required] enable binary logging
server-id=226//[must be] server unique ID, default is 1, usually take IP last paragraph

4. Restart MySQL for two servers
/etc/init.d/mysql restart

5. Establish an account on the primary server and authorize slave:
#/usr/local/mysql/bin/mysql-uroot-pmttang
Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ ' percent ' identified by ' q123456 '; Generally do not use the root account,% means that all clients may be connected, as long as the account, password is correct, here can be specific client IP instead, such as 192.168.145.226, enhance security.

6, log on to the master server MySQL, query the status of Master
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              308 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

7. Configure the slave from the server:
Mysql>change Master to master_host= ' 192.168.145.222 ', master_user= 'Mysync', master_password= ' q123456 ',
Master_log_file= ' mysql-bin.000004 ', master_log_pos=308; Be careful not to disconnect, there are no single quotes around 308 digits.

Mysql>start slave; To start the Copy from Server feature

8. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event
master_host:192.168.2.222//Primary server address
Master_user:mysync//Authorization account name, try to avoid using root
master_port:3306//Database port, some versions do not have this line
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:600//#同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos
relay_log_file:ddte-relay-bin.000003
relay_log_pos:251
relay_master_log_file:mysql-bin.000004
Slave_io_running:yes//This status must be Yes
Slave_sql_running:yes//This status must be Yes
......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

The above operation process, the master and slave server configuration is complete.

9, the master-slave server test:

master server MySQL, build the database, and create a table in this library to insert a piece of data:

mysql> CREATE DATABASE hi_db;
Query OK, 1 row Affected (0.00 sec)

mysql> use hi_db;
Database changed

Mysql> CREATE TABLE HI_TB (ID int (3), name char (10));
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO HI_TB values (001, ' Bobu ');
Query OK, 1 row Affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| hi_db |
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

MySQL Query from server:

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| Information_schema |
|       hi_db | I ' M here, you see?
| MySQL |
| Test |

+--------------------+
4 rows in Set (0.00 sec)

Mysql> Use hi_db
Database changed
Mysql> select * from HI_TB; View new specific data on the primary server
+------+------+
| ID | name |
+------+------+
| 1 | Bobu |
+------+------+
1 row in Set (0.00 sec)

10. Completion:
Write a shell script, using Zabbix to monitor the Slave two yes (Slave_io and slave_sql process), if only one or 0 yes, it indicates the master-slave problem, send SMS alarm.

MySQL master-slave database replication