MySQL replication is quite complex compared to NoSQL databases such as Mongodb,redis!
Overview
The primary server first logs data changes to the primary log, then reads the master log on the primary server through the I/O thread from the server, writes it to the relay log from the server, and then the SQL thread reads the relay log and replays it from the server to achieve MySQL replication. As shown in the following illustration:
The entire process is reflected from the server, corresponding to three sets of log information, can be viewed from the server with the following command:
Copy Code code as follows:
Mysql> show SLAVE STATUS;
Master_log_file & Read_master_log_pos: The next transmitted primary log information.
Relay_master_log_file & Exec_master_log_pos: The next execution of the primary log information.
Relay_log_file & relay_log_pos: Next relay log information executed.
Understanding the meaning of these log messages is critical to resolving the problem, which is explained in detail later in this article.
Installation
Create a replication account on the primary server first:
Copy Code code as follows:
Mysql> GRANT REPLICATION SLAVE on *.*
To ' <SLAVE_USER> ' @ ' <SLAVE_HOST> '
Identified by ' <SLAVE_PASSWORD> ';
Note: For security and flexibility considerations, do not put root, etc. SUPERPermission user as the replication account. Then set the primary server configuration file (default:/ETC/MY.CNF):
Copy Code code as follows:
[Mysqld]
server_id = 100
Log_bin = Mysql-bin
Log_bin_index = Mysql-bin.index
Sync_binlog = 1
Innodb_flush_log_at_trx_commit = 1
Innodb_support_xa = 1
Note: Be sure to ensure that the principal and subordinate server server_id unique, avoid conflict.
Note: If you do not specify Log_bin , the lack of the provincial capital using the hostname as the name, so that once the hostname has changed, it will be problematic, so the recommended designation Log_bin (from the server relay_log the same problem).
Note:sync_binlog,innodb_flush_log_at_trx_commit,innodb_support_xa Three options are set for security purposes, is not a required option for replication.
Then set the From server configuration file (default:/ETC/MY.CNF):
Copy Code code as follows:
[Mysqld]
server_id = 200
Log_bin = Mysql-bin
Log_bin_index = Mysql-bin.index
Relay_log = Mysql-relay-bin
Relay_log_index = Mysql-relay-bin.index
READ_ONLY = 1
Skip_slave_start = 1
Log_slave_updates = 1
Note: If the user has Super permission, the read_onlyInvalid.
Note: If you have skip_slave_start, replication will not start from the server unless you use the start slave command.
Note: setting log_slave_updatesto log logs from the server helps to switch from the master to the main when necessary.
The most important step below is how to clone a copy of the primary server's data:
If the database is using a MyISAM table type, you can do the following:
Copy Code code as follows:
shell> mysqldump--all-databases--master-data=1 > Data.sql
Note: Master-dataoption is missing the provincial capital open Lock-all-tables, and write Change MASTER toStatement.
If the database is using a InnoDB table type, you should use single-transcation:
Copy Code code as follows:
shell> mysqldump--all-databases--single-transaction--master-data=1 > Data.sql
With the data file, transfer to the server and import:
Copy Code code as follows:
If the volume of data is very large, the mysqldump will be very slow, then directly copy data files can save a lot of time:
Before copying, lock the data before obtaining the relevant log information (FILE & POSITION):
Copy Code code as follows:
Mysql> FLUSH TABLES with READ LOCK;
Mysql> show MASTER STATUS;
Next copy the data file, if it is MyISAM table type, direct copy can be, if it is InnoDB table type, must stop the MySQL service copy, otherwise the copy file may not be used. Copy the data file directly to the data directory from the server.
Finally, you will need to specify the log information again:
Copy Code code as follows:
Mysql> Change MASTER to
Master_host= ' <MASTER_HOST> ',
Master_user= ' <SLAVE_USER> ',
Master_password= ' <SLAVE_PASSWORD> ',
Master_log_file= ' <FILE> ',
master_log_pos=<position>;
Note: Do not set Master_user and Master_password in the MY.CNF configuration file because the information in the Master.info file that the change MASTER will generate is ultimately in effect.
Copying data files directly on the primary server is fast, but requires locking the table or stopping the service, which can affect online services. If you have previously had a from server, then you can use the old server as a parent to clone new from the server:
First query log information from the old server:
Copy Code code as follows:
Mysql> show SLAVE STATUS;
What we need is one of the Relay_master_log_file & Exec_master_log_pos.
The data is then obtained from the previous method on the old server and restored on the new server.
Next, set the log information on the new server:
Copy Code code as follows:
Mysql> Change MASTER to
Master_host= ' <MASTER_HOST> ',
Master_user= ' <SLAVE_USER> ',
Master_password= ' <SLAVE_PASSWORD> ',
Master_log_file= ' <Relay_Master_Log_File> ',
master_log_pos=<exec_master_log_pos>;
Whichever way you do it, remember to start replication from the server and check to see if it's working correctly:
Copy Code code as follows:
Mysql> START SLAVE;
Mysql> show SLAVE STATUS;
If both IO threads and SQL threads Show Yes, you can thank God:
Slave_io_running correspondence: Master_log_file & Read_master_log_pos
Slave_sql_running correspondence: Relay_master_log_file & Exec_master_log_pos
If no is displayed, some previous configuration steps have been faulted, or there is a problem with the corresponding log file.
Fault
Question: Master-slave replication has more than a stop, what should I do?
Answer: Replication errors are mostly due to log errors, so first of all to find out whether the primary log error or relay log error, from the error message generally can be judged, if you can not use similar to the following Mysqlbinlog command:
Copy Code code as follows:
shell> Mysqlbinlog <MASTER_BINLOG_FILE> >/dev/null
shell> Mysqlbinlog <SLAVE_BINLOG_FILE> >/dev/null
If there are no errors, there will be no output, and conversely, if there is an error, it will be displayed.
If it is a primary log error, you need to use the SET GLOBAL Sql_slave_skip_counterAs follows:
Copy Code code as follows:
mysql> SET GLOBAL sql_slave_skip_counter = 1;
Mysql> START SLAVE;
Note: If you have multiple errors, you may need to perform multiple times (reminders: the master-slave server data may be inconsistent).
If it is a relay log error, as long as the log information in the show SLAVE status result is reset from the server to the MASTER to, the system discards the current relay log and downloads it again:
Copy Code code as follows:
Mysql> Change MASTER to
Master_log_file= ' <Relay_Master_Log_File> ',
master_log_pos=<exec_master_log_pos>;
Mysql> START SLAVE;
As for why Relay_master_log_file & Exec_master_log_pos are used, see Overview.
Problem: Primary server down, how to upgrade from the server to the main server?
Answer: In a master multiple from the environment total, you need to choose the latest data from the server to do the new master server. As shown in the following illustration:
Promote from server to primary server
In a primary (Server1) two-(Server2, SERVER3) environment, Server1 downtime, wait until Server1 and Server2 synchronize the logs before they are down, comparing Master_log_file and Read_master_ Log_pos can tell who is fast and who is slow, because Server2 data synchronized from Server1 (1582) is newer than SERVER3 data synchronized from Server1 (1493), so the Server2 should be promoted as the new primary server, so Server3 What parameters should be used when the change MASTER to Server2? 1582-1493=89, and Server2 's last binary log position is 8167, so the answer is 8167-89=8078.
Skills
Tables in the master-slave server can use different table types. For example, the main server can use the InnoDB table type, providing transaction, row lock and other advanced features, from the server can use the MyISAM table type, less memory consumption, easy backup advantages. There is also an example of if a host server with many from the server, it is bound to affect its performance, at this time can take out a server as a proxy from the server, using the Blackhole table type, only log, do not write data, by it with more than one from the server, thereby enhancing performance.
Tables in the master-slave server can use different key types. For example, the main server with InnoDB, the key with varchar words to save space, using MyISAM from the server, the key with char to improve speed, because the MyISAM has a static table said.
Tables in the master-slave server can use different indexes. Primary server is mainly used to deal with write operations, so in addition to primary key and unique index, such as the guarantee data relationship index can not be added, from the server generally used to cope with read operations, so you can set the index for query characteristics, and further, different from the server can be different from the query set different indexes
Tools
There are some excellent tools to make your copy work more effective, please refer to the respective documentation for details:
Multi-master Replication Manager for MySQL
Percona Xtrabackup
Maatkit
Tungsten-replicator
In addition, there are many interesting items in Google project hosting that can be searched with mysql+replication tags.
Note: This article refers to the relevant content in the books listed below:
High performance mysql:optimization, Backups, Replication, and more
MySQL high availability:tools for building robust Data Centers
(Source: Fire Ding notes )
