Mysql master-slave replication technology (test) _ MySQL-mysql tutorial

Mysql master-slave replication technology (test) bitsCN.com

Mysql master-slave replication technology (test)

Start configuration:

Step 1: Create a replication account

Each slave uses a standard MySQL User name and password to connect to the master. The user who performs the copy operation will grant
REPLICATIONSLAVE permission.

The username and password are stored in the master.info text file. If you want to create a repl user

Mysql> grant replication slave, replication client on *. * TO repl @ '2017.
168.1.% 'identified BY '20140901 ';

Step 2: Configure My. cnf

Configure My. cnf of the Master. the default location of this file is/etc/my. cnf.

Next, configure the master, including opening the binary log and specifying a unique servr ID. For example, in the configuration file
Add the following values:

[Mysqld]

Server-id = 1

Log-bin = mysql-bin

Restart mysql, service mysql restart,

Log on to mysql-uroot-p

Run show master status and the output is as follows:

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000002 | 106 |

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

1 row in set (0.00 sec)

Configure My. cnf for Slave. the default location of this file is/etc/my. cnf.

The Slave configuration is similar to that of the master. you also need to restart the slave MySQL. As follows:

Server-id = 2

Log-bin = mysql-bin

Relay_log = mysql-relay-bin

Log_slave_updates = 1

Read_only = 1

Server-id is required and unique. Slave does not need to enable binary logs, but in some cases
For example, if the slave is another master of the slave, bin_log must be set. Here, we have enabled binary
Log, and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ).

Relay_log: configure the relay log. log_slave_updates indicates that slave writes the replication event to its own binary log (
See its usefulness ).

Some people enable the slave binary log, but do not set log_slave_updates, and then check whether the slave data is changed.
This is an incorrect configuration. Therefore, use read_only whenever possible, which prevents data changes (except for special threads ). However,
Read_only is very useful, especially for applications that need to create tables on slave.

Restart mysql, service mysql restart,

Log on to mysql-uroot-p

Step 3: Start slave

Next, let slave connect to the master and begin to redo the events in the master binary log. You should not use the configuration
File, but the change master to statement should be used. this statement can completely replace the modification TO the configuration file,
In addition, it can specify different Masters for the slave without stopping the server. As follows:

Mysql> change master to MASTER_HOST = '192. 168.60.73 ', MASTER_USER = 'repl', MASTER_PASSWORD = '2016', MASTER
_ LOG_FILE = 'MySQL-bin.000002 ', MASTER_LOG_POS = 0;

The value of MASTER_LOG_POS is 0 because it is the start position of the log. Then, you can use SHOW SLAVE STATUS
Statement to check whether the slave settings are correct:

Mysql> show slave status/G

* *************************** 1. row ***************************

Slave_IO_State:

Master_Host: server1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_ SQL _Running: No

... Omitted...

Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, and Slave_ SQL _Running indicate that the replication process has not started yet.
The log location is 4 rather than 0, because 0 is the start position of the log file, not the log location. In fact, MySQL knows
The first event is located at 4.

To start replication, you can run:

Mysql> start slave;

Mysql> show slave status/G

Run show slave status to view the output result:

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: server1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 164

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 164

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

... Omitted...

Seconds_Behind_Master: 0

Note that slave's I/O and SQL threads have started to run, and Seconds_Behind_Master is no longer NULL. Logs
It means that some events are obtained and executed. If you modify it on the master, you can view it on slave.
Changes to the location of various log files. Similarly, you can see changes in data in the database.

You can view the status of the master and slave threads. On the master, you can see the connection created by the slave I/O thread:

Mysql> show processlist/G

* *************************** 1. row ***************************

Id: 1

User: root

Host: localhost: 2096

Db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

* *************************** 2. row ***************************

Id: 2

User: repl

Host: localhost: 2144

Db: NULL

Command: Binlog Dump

Time: 1838

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

2 rows in set (0.00 sec)

This is basically done here. As for the later enhancement operations, I will add them separately.

During Mysql replication, you can restrict replication (master)

Binlog-do-db = work # Copy only

Binlog_ignore_db = mysql # Copying is not allowed

Replicate_Do_DB = mysql

Replicate-ignore-db = mysql

There are also several common commands

Flush mater; # clear garbage

Flush slave; # clear garbage

Start slave; # start Service

Reset slave; # resetting services

Stop slave; # stop the service

The above replication method assumes that the actual environment is not very practical when the new Master and slave are created. for example
What if a new slave is added to the Master that has been running in the group for a long time?

In this way, we need to prioritize data processing.

1. the Master database is locked to avoid data duplication.

Flush tables with read lock;

2. use mysqldump on another connection to create a dump of the database you want to copy:

Shell> mysqldump -- all-databases -- lock-all-tables> dbdump. db

3. release the lock on the table.

Mysql> unlock tables;

In the above method, part 1 needs to copy the database to the new slave for restoration, so that the Master and the new Slave data sources are consistent.

In fact, there are many methods in Part 1, and you can also use Mysql software for synchronization, for example: Navicat for Mysql

The method is not unique, as long as the results are consistent.

4. after the old data synchronization is completed, enter

Mysql> show master status;

# Obtain:

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000024 | 38113 | mysql, test |

1 row in set (0.00 sec)

5. Then, enter the content of the preceding Slave startup step based on the content in the table above.

Change master to MASTER_HOST = '192. 168.60.73 ', MASTER_USER = 'repl', MASTER_PASSWORD = '2016 ',
MASTER_LOG_FILE = 'MySQL-bin.000002 ', MASTER_LOG_POS = 0;

6. start slave

Start slave;

7. check after startup.

Mysql> show slave status/G ***************************
1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.171

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000024

Read_Master_Log_Pos: 38255

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 393

Relay_Master_Log_File: mysql-bin.000024

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql, wdcpdb, mysql, wdcpdb

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 38255

Relay_Log_Space: 548

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 0

Last_ SQL _Error:

1 row in set (0.00 sec)

Complete

Main problems in the testing process:

1. configure Mysql first

2. copy the Mysql database and table framework before enabling replication.

(Check whether the table can be copied !)

BitsCN.com