Original works, please indicate the address of this article for reprinting
Operating system: CentOS Linux release 7.3.1611 (Core)
Database: MariaDB-10.2.6-linux-glibc_214-x86_64
Main server: 10.10.10.56
Slave server: 10.10.10.163
1. One way master slave synchronization is what we need to do today
For the installation of the MariaDB (MySQL) database, see Centos7.3 x64 Bit Binary Installation (MySQL) MariaDB 10.1.20 Database Production Next, start the configuration process of the MariaDB (MySQL) server cluster.
2. Modify or add the following information in the MariaDB (MySQL) configuration file
vim /etc/my.cnf
Master slave general configuration
Binlog format=mixed # The mode of binary logging (enabled by default for higher versions) Binlog checksum=CRC32 # Enables the host to write verification for events written to the binary log (enabled by default for higher versions) Sync master info=1 # MariaDB relies on the operating system to refresh the master.info file to disk. Sync_relay_log_info=1 # MariaDB relies on the operating system to refresh the relay-log.info file to the disk. Expire_logs_days=7 # The number of days the log file expires. The default value is 0, indicating that it does not expire Master verify checksum=1 # master server validation Slave sql verify checksum=1 # Validate from server
3. In addition to the general configuration, the master server also needs to add the following codes
Server id=56 # MySQL server ID, not duplicate Log bin=mysql bin # Binary log (enabled by default) Sync binlog=1 # set by the master server for transaction security log-bin-index = mysql-bin
4. In addition to general configuration, slave server slave also needs to add the following code
server-id = 163 Relay log=relay bin # Relay log Slave parallel threads=2 # Set the number of SQL threads from the server #Replicate do db=renwoleblogdb # Replicate the specified database and write multiple rows Replicate ignore db=mysql # Database not backed up, multiple writes and multiple rows Relay_log_recovery=1 # It can be used after the slave crashes to prevent damaged relay log processing. Log slave updates=1 # slave writes replication events into its own binary log relay-log-index = relay-bin
In addition, it is not necessary to enable binary logs from the MySQL slave server, but in some cases, it must be set, for example; If slave is the master of other slave, bin_log must be set. I will start it by default.
5. The above is just a brief introduction of the role of each parameter. The specific settings of these parameters need to be adjusted according to the actual situation of the user, and the details can be learned from the official
Replication and Binary Log Server System Variables
//mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/
For the compatibility of system variables, see the official
MariaDB and MySQL Compatibility
//mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/
6. Master authorization configuration of master server
Create a special account on the primary MariaDB server and authorize database permissions, as well as remote access from the server IP
# mysql -uroot -p Enter password: [Enter your MySQL password] MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'renwoleuseracc'@'%' IDENTIFIED BY 'renwoleuserpass'; // Create a special backup account for Slave MariaDB [(none)]> flush privileges; // Refresh MySQL permissions MariaDB [(none)]>SELECT DISTINCT CONCAT ('User: '', user, '' @ '', host, '' ';') AS query FROM mysql.user;//View the authorization status MariaDB [(none)]> flush tables with read lock; // Lock the database to prevent the master value from changing MariaDB [(none)]> show master status; // Get master status value +-----------------+----------+------------+-----------------+ | File |Position |Binlog_Do_DB|Binlog_Ignore_DB | +-----------------+----------+------------+-----------------+ | mysql-bin.000006| 627 | | | +-----------------+----------+------------+-----------------+ 1 row in set (0.00 sec)
7. Once the correct Binlog bit (file name and offset) at the time of backup is obtained, the BINLOG_GTID_POS() function can be used to calculate the GTID
MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.000006", 627); +------------------------------------------+ | BINLOG_GTID_POS('mysql-bin.000006', 627) | +------------------------------------------+ | 0-56-4 | +------------------------------------------+ 1 row in set (0.01 sec)
8. Slave configuration
As the official said, starting from the MariaDB 10.0.13 version, mysqldump will automatically complete this work, and write the GTID in the export file, as long as you set – master data or – dump slave and set – gtid at the same time.
In this way, the new SLAVE can set the start position of replication by setting the value of @ @ gtid_slave_pos, use CHANGE MASTER to transfer this value to the main database, and then start replication:
# mysql -uroot -p Enter password: [Enter your MySQL password] MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-56-4"; MariaDB [(none)]>change master to master_host=10.10.10.56 ', MASTER_PORT=3306, master_user='renwoleuseracc', master_password='renwoleuserpass', master_use_gitid=slave_pos;//Perform master-slave authorization MariaDB [(none)]> START SLAVE; // Start Slave MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.56 Master_User: renwoleuseracc Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 627 Relay_Log_File: relay.000035 Relay_Log_Pos: 537 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ... ... Using_Gtid: Slave_pos Gtid_IO_Pos: 0-56-4
9. If Slave_IO_Running and Slave_SQL_Running are both YES, it indicates that the service has been run, and the Using_Gtid column determines whether the GTID values are consistent.
explain:
Master_host indicates the authorized address of the master
MASTER_PORT MySQL port
Master_user indicates the master authorization account
Master_password indicates the password
Master_use_gtid GTID variable value
10. Next, unlock the primary server database table
MariaDB [(none)]> unlock tables; // Unlock Data Table MariaDB [(none)]> show slave hosts; // View slave server connection status MariaDB [(none)]> show global status like "rpl%"; // View client
11. View all relevant parameters of relay from the server Slave
MariaDB [(none)]> show variables like '%relay%';
12. The master-slave configuration has been completed. Now whether you add, modify, delete, or check on the primary server, you will synchronize it to the secondary server, and you can conduct relevant tests according to your own needs.
About the reset syntax of master slave
Reset the core syntax of master
RESET MASTER; This means that executing the RESET MASTER will delete all binary log files and create a blank binary log file with a number suffix of. 000001. The RESET MASTER will not affect the working status of the SLAVE server. Therefore, executing this command will cause Slave to fail to find the master's binlog, which will cause synchronization failure.
Reset slave's core syntax
RESET SLAVE; Means; RESET SLAVE will clear the synchronization location on the slave and delete all old synchronization relay log files, but the slave service must be stopped before resetting (STOP SLAVE)
When I have time, I will introduce the GTID based semi synchronous master-slave article, which is also required to be recorded in production for future use.
More information://mysql.taobao.org/monthly/2016/02/08/