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