Shulou(Shulou.com)06/01 Report--

Mysql's built-in replication function is the basis for building large, high-performance applications. Distribute the data of Mysql to multiple systems. The mechanism of this distribution is to copy the data of one host of Mysql to another host (slaves) and execute it again. During replication, one server acts as the master server, while one or more other servers act as the slave server. The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for the primary server to notify the new update.

Please note that when you replicate, all updates to the replicated tables must be made on the primary server. Otherwise, you must be careful to avoid conflicts between user updates to the tables on the master server and updates to the tables on the slave server.

Types of replication supported by mysql: (1): statement-based replication: SQL statements executed on the master server and the same statements executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.

Row-based replication is automatically selected when it is found that exact replication is not possible.

(2): line-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

(3): mixed-type replication: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

Problems solved by replication MySQL replication technology has the following characteristics:

(1) data distribution (Data distribution)

(2) load balancing (load balancing)

(3) backup (Backups)

(4) High availability and fault tolerant line High availability and failover

How replication works overall, there are three steps to replication:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and changes the data that reflects itself.

Replication process:

Specific implementation:

1. The master and slave servers perform the following operations:

1.1, version consistent

Initialize the table and start mysql in the background

1.3.Moving the password of root

2. Modify the master server master:

# vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / [must] enable binary logging

Server-id=222 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

3. Modify the slave server slave:

# vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / [not required] enable binary logging

Server-id=226 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

4. Restart the mysql of two servers

/ etc/init.d/mysql restart

5. Establish an account on the master server and authorize slave:

# / usr/local/mysql/bin/mysql-uroot-pmttang

Mysql > GRANT REPLICATION SLAVE ON *. * to 'mysync'@'%' identified by' q123456accounts; / / generally, no root account is used.% means that all clients can be connected. As long as the account number and password are correct, you can use specific client IP here, such as 192.168.145.226, to enhance security.

6. Log in to the mysql of the master server and 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 after performing this step to prevent the status value of the master server from changing

7. Configure slave server Slave:

Mysql > change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456'

Be careful not to disconnect master_log_file='mysql-bin.000004',master_log_pos=308; / /. There are no single quotation marks around the number 308.

Mysql > start slave; / / start the copy from server function

8. Check the status of replication 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 / / Master server address

Master_User: mysync / / authorized account name, avoid using root as much as possible

Master_Port: 3306 / / database port, which is not available in some versions

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 600 / / # the location where binary logs are read synchronously, which is 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 be running normally, that is, the YES state, otherwise they are all in the wrong state (for example, one of the NO is wrong).

The configuration of the master and slave server is completed in the above operation process.

9. Master-slave server test:

The master server Mysql, set up the database, and insert a piece of data into the library by creating a table:

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)

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)

Query from server Mysql:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | hi_db | / / Itemm here, you see? |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > use hi_db

Database changed

Mysql > select * from hi_tb; / / View the specific data added on the master server

+-+ +

| | id | name |

+-+ +

| | 1 | bobu |

+-+ +

1 row in set (0.00 sec)

10. Complete:

Write a shell script to monitor two yes (Slave_IO and Slave_SQL processes) of slave with zabbix. If you find that there is only one or zero yes, it indicates that there is something wrong with the master and slave. Send a text message alarm.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.