Install and configure Mysql master-slave MySQL

Install and configure Mysql master/slave bitsCN.com

Environment:
The MySQL database version on the master and slave servers is 5.1.34.
Host IP: 192.168.0.1
Slave IP address: 192.168.0.2
1. MySQL master server configuration
1. edit the configuration file/etc/my. cnf
# Make sure it is as downstream
Server-id = 1
Log-bin = mysql-bin
Binlog-do-db = mysql # Name of the database to be backed up. if multiple databases are backed up, set this option again.
Binlog-ignore-db = mysql # Name of the database that does not need to be backed up. if multiple databases are backed up, set this option again.
Log-slave-updates # this parameter must be added; otherwise, the updated records will not be added to the binary file.
Slave-skip-errors # indicates that the error is skipped and the copy operation continues.
2. create a user
Mysql> grant replication slave on *. * to slave@192.168.0.2 identified by '000000 ′;
# Grant replication slave on *. * to 'username '@ host' identified by 'password ';
# Connection test on Slave: mysql-h 192.168.0.1-u test-p
3. lock the master database table
Mysql> flush tables with read lock;
4. display master database information
Record File and Position, which will be used in slave database settings
==================================
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_do_db | Binlog_ignore_db |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 106 |
+ ------------------ + ---------- + -------------- + ------------------ +
5. open another terminal and package the master database
Cd/usr/local/mysql # mysql database Directory
Tar zcvf var.tar.gz var
======================================
II. MySQL slave server configuration
1. data packets are transmitted to and unbound from the master database.
# Cd/usr/local/mysql
# Scp 192.168.0.1:/usr/local/mysql/var.tar.gz.
# Tar zxvf var.tar.gz
2. view and modify the var folder permissions
# Chown-R mysql: mysql var
3. edit/etc/my. cnf
Server-id = 2
Log-bin = mysql-bin
Master-host = 192.168.0.1
Master-user = slave
Masters-password = 111111
Master-port = 3306
Replicate-do-db = test # Name of the database to be backed up
Replicate-ignore-db = mysql # ignored database
Master-connect-retry = 60 # time difference (in seconds) when the master server is disconnected from the server)
Log-slave-updates # this parameter must be added; otherwise, the updated records will not be added to the binary file.
Slave-skip-errors # indicates that the error is skipped and the copy operation continues.
4. verify the connection to the MASTER
# Mysql-h192.168.0.1-uslave-ppassword
Mysql> show grants for slave@192.168.0.2;
5. set synchronization on SLAVE
Set MASTER_LOG_FILE to the MASTER database, and MASTER_LOG_POS to the Position of the MASTER database.
======================================
Mysql> slave stop;
Mysql> change master to MASTER_HOST = 192.168.0.1, MASTER_USER = slave, MASTER_PASSWORD = 111111, MASTER_LOG_FILE = mysql-bin.000001, MASTER_LOG_POS = 106;
6. start the SLAVE service
Mysql> slave start;
7. view the SLAVE status
Mysql> show slave statusg;
The values in the Slave_IO_Running and Slave_ SQL _Running columns are "Yes", indicating that the Slave I/O and SQL threads are running properly.
8. unlock the master database table
Mysql> unlock tables;
The master-slave database is successfully built. You can insert data into the master database to test whether the synchronization is normal.
Common errors and solutions:
Troubleshooting of common problems:

1: show slave statusG on the slave database; the following situations occur,
Slave_IO_Running: Yes
Slave_ SQL _Running: No
Seconds_Behind_Master: NULL

Cause:
A. The program may write data on slave.
B. It may also be caused by transaction rollback after the server load balancer instance restarts.

Solution:

Enter master

Mysql> show master status;
+ ---------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ---------------------- + ---------- + -------------- + ------------------ +
| Mysql-bin.000040 | 324 |
+ ---------------------- + ---------- + -------------- + ------------------ +
Then execute Manual synchronization on the slave server.

Slave stop;
Change master
Master_host = 10.14.0.140,
Master_user = repl,
Master_password = 111111,
Master_port = 3306,
Master_log_file = mysql-bin.000040,
Master_log_pos = 324;
Slave start;
Show slave statusG;

2. symptom: The slave Database cannot be synchronized. The show slave status shows that Slave_IO_Running is No, and Seconds_Behind_Master is null.

Solution: Restart the primary database.

Service mysql restart

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 98 |
+ ------------------ + ---------- + -------------- + ------------------ +
Slave stop;
Change master to maid = mysql-bin.000001, Master_Log_Pos = 98
Slave start;
Or:
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave;

This problem mainly occurs in the master database. in actual operations, I need to restart the master database and then restart slave to solve this problem. in this case, I must restart the master database.
1. the primary and secondary databases are synchronized mainly through binary logs.
2. when starting the secondary database, you must synchronize the data and delete the master.info file in the log directory. Because master.info records the information of the master database to be connected last time. if it is not deleted, it does not work even if it is modified in my. cnf. Because the read information is still in the master.info file.

In the mysql replication environment, there are eight parameters for us to control. we need to copy or ignore the databases or tables that are not copied as follows:
The following two items must be set on the Master:
Binlog_Do_DB: set which databases need to record Binlog
Binlog_Ignore_DB: set where the database does not need to record Binlog

The advantage is that the Binlog record on the Master end reduces the I/O volume and network I/O, and reduces the I/O threads and SQL threads on the slave end, greatly improving the replication performance,
The disadvantage is that mysql does not determine whether to copy an event based on the DB where the query that generates the event is located, instead, it is based on the default database (that is, the database name specified during logon or the database specified in "use database") where the query is executed ), the IO thread will read the event to the slave IO thread only when the default DB is exactly the same as the DB set in the configuration. therefore, if the data in a Table in the database to be copied is changed when the default database and the database to be copied are different, the event will not be copied to Slave, in this way, the Slave data is inconsistent with the Master data. similarly, if the data in the database that does not need to be copied is changed under the default database, it will be copied to the slave end. when the slave end does not have the database, this will cause a replication error and stop.

The following six items must be set on slave:
Replicate_Do_DB: specifies the database to be copied. multiple databases are separated by commas (,).
Replicate_Ignore_DB: sets the databases that can be ignored.
Replicate_Do_Table: specifies the Table to be copied.
Replicate_Ignore_Table: sets the Table that can be ignored.
Replicate_Wild_Do_Table: The function is the same as Replicate_Do_Table, but can be set with wildcards.
Replicate_Wild_Ignore_Table: The function is the same as Replicate_Do_Table. the function is the same as Replicate_Ignore_Table and can contain wildcards.

The advantage is that the replication filtering mechanism is set on the slave side to ensure that the data inconsistency between the Slave and Master is not caused by the default database problems or the replication error occurs.
The disadvantage is that the performance is worse than that on the Master side. the reason is: no matter whether replication is required or not, the event will be read by the IO thread to the Slave end. This not only increases the network IO volume, but also increases the Relay Log write volume for the IO thread on the Slave end.
Synchronization principles
MySQL Replication tracks all database changes (updates, deletions, and so on) in binary logs based on the master server ).
MySQL uses three threads to complete Replication. the specific distribution is the master thread and the last two threads;
The main thread can be understood as the Binlog Dump thread in the output of show processlist on the master server, IO thread and SQL thread on the slave server;
The master server creates the binlog to send the content to the slave server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to the relay-log file in the server data directory, the SQL thread is used to read relay logs and execute updates contained in the logs.
MySQL Replication is unidirectional and asynchronous
The MySQL synchronization mechanism records all database updates and deletions based on the master node in binary logs. Therefore, to enable the synchronization mechanism, binary logs must be enabled on the master node. Each slave receives an update operation recorded in the binary log on the master. Therefore, a copy of this operation is executed on the slave. It should be very important to realize that binary logs only record update operations at the beginning of enabling binary logs. All slave data must be copied from the master when binary logs are enabled. If the data on the slave is inconsistent with that on the master node when binary logs are enabled during synchronization, the slave synchronization will fail. One of the methods to copy DATA on the master is to execute the load data from master statement on slave. However, you must note that load data from master is available only after MySQL 4.0.0, and only MyISAM tables on the master are supported. Similarly, this operation requires a global read lock, so that no update operation will be performed on the master when the log is transferred to the slave. When the free lock table hot backup is implemented (in MySQL 5.0), the Global read lock is unnecessary. Because of these restrictions, we recommend that you run the load data from master statement only when the DATA on the MASTER is small, or allow a long read lock on the master. Since each system loads data from the MASTER at different speeds, a good measurement rule is that 1 MB of DATA can be copied per second. This is just a rough estimate, but the master and slave are both Pentium 100 MHz machines and can achieve this speed when connected with a MBit/s network. After the master data has been fully copied on the slave, you can connect to the master and wait for updates. If the master is disconnected from the server or slave, slave periodically tries to connect to the master until it can be reconnected and waits for updates. Retry interval