MySQL master-slave synchronization settings and synchronization error handling

MySQL master-slave synchronization settings and synchronization error handling Note: 1. The database versions of the two servers should be the same. If they are different, the slave server version should be higher than the master server version. 2. MySQL performs real-time data synchronization, essentially synchronizing mysql actions to the Slave server, rather than synchronizing substantive data. Therefore, the data at both ends must be consistent before synchronization starts. Master server: 192.168.0.1 Slave server: 192.168.0.2 = when the Master node stops running, Mysql synchronization configuration = 1. Master server settings: 1. Modify/etc/my. cnf adds the server-id = 1 log-bin SQL-bin-update-same parameter to the [mysqld] section. 2. Creates a connection account for the Slave server, used to synchronize mysql> grant file on *. * TO backup@192.168.1.2 identified by '200'; 3. restart mysql service: service mysqld restart at this time because the log-bin parameter is added, index is generated, under the/var/lib/mysql directory. index file records the change log of the database. ii. Slave server settings: 1. Modify/etc/my. cnf is added to mast in the [mysqld] Section Er-host = 192.168.0.1 master-user = backup master-password = 1234 master-port = 3306 server-id = 2 master-connect-retry = 60 preset retry Interval 60 seconds replicate-do -db = vbb tells slave to update replicate-ignore-db = mysql only for vbb databases # replicate-ignore-table = vbb for non-synchronized databases. users # Do not synchronize the users table log-slave-updates = 1 2. Restart the Slave server mysql service 3. The Slave server reads data from the master server, and enable synchronization 1. mysql> flush tables with read lock; # first execute this, LOCK the MASTER server 2, mysql> load data from master # statement, You must grant the Global FILE and SELECT permissions, only for the MyISAM engine, and useless for InnoDB tables. 3. If the data is InnoDB, You need to export the table structure and data separately on the server and then copy the data to the Slave server. Export the entire database structure and data: mysqldump-u username-p password-h host database> filename. SQL export the data and structure of a table: mysqldump-u username-p password-h host database table> filename. SQL only exports data from tables in the database: mysqldump-u username-p password-h host-T database table> filename. SQL only exports the table structure in the database: mysqldump-u username-p password-h host-d database table> filename. SQL 4. Enable mysql synchronization> slave start after the data on both ends is consistent; 5. view the synchronization information of the Slave server mysql> show slave status \ G, the values of Slave_IO_Running and Slave_ SQL _Running are both" Yes ", which indicates that the Slave I/O and SQL threads are running normally. 6. Release the lock mysql> unlock tables for the Master server. === if the Master is still running, configure Slave === without stopping it. Note: the configuration of the master-slave server is the same as that of the master-slave server. 1. First, check the synchronization log information on the Master server mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | metric | + metric + ---------- + -------------- + metric + | mysql-bin.000054 | 680 | mysql | + metric + ---------- + -------------- + ------------------ + you can see that the log file is: mysql-bin.000054, synchronization point is 680 2. Configure synchronization mysql> Slave sto on the slave server P; # mysql Default synchronization start mysql> change master to MASTER_HOST = '2017. 168.0.1 ', MASTER_USER = 'backup', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000054 ', MASTER_LOG_POS = 1234; # configure synchronization point mysql> slave start; # enable synchronization 3. View synchronization information mysql> show slave status \ G. You can see that the values of Slave_IO_Running and Slave_ SQL _Running are "Yes ", this indicates that Slave's I/O and SQL threads are running normally. 4. Complete the MYSQL Server replication configuration. This is based on the MYSQL copy document that I have read before, and then experiment with it myself. the configuration function is relatively simple. environment: master server: redhat9mysql 5.0.16 machine name: dbmasterIP: 192.168.0.111 slave server: redhat9mysql5.0.16 machine name: dbslaveIP: 192.168.0.100 configuration process, it is just a concrete and clear point of attention .. if you are interested, you can compare and read it for better understanding. 1. check the MYSQL version of the two servers and run the mysql-V command. Note: The binlog format of different MySQL versions (Binary logs) may be different, so it is best to use the same version. if the requirements are not met, you must ensure that the Master version cannot be higher than the Slave version 2. set a connection account for the server on the master server. This account must be granted the replication slave permission. I did not do this simply by using the root account. the normal procedure is as follows: assume that your domain is mydomain.com and you want to create an account whose username is repl, the slave server can use this account to access the master server from any host in your domain using the slavepass password. To create this account, you can use the GRANT statement mysql> grant replication slave on *. *-> TO 'repl' @ '% .mydomain.com' identified by 'slavepass '; to execute the "load table from master" or "load data from master" Statement on Slave, you must GRANT the Global FILE and SELECT permissions: mysql> grant file, SELECT, replication slave on *. * TO 'repl' @ '% .mydomain.com' identified by 'rep '; 3. execute the flush tables with read lock Statement on the master server to refresh the table and prevent write operations.: mysql> flush tables with read lock; To take snapshots of the data on the ECS instance. (In this case, do not exit the mysql prompt. If you quit, it will be unlocked. open an ssh window and perform the archive packaging operation described below. The simplest way to create a snapshot is to use the archive program to perform binary backup on the database in the data directory on the master server. You need to use tar to create archive files including all databases, enter the data directory of the master server, and then execute the command: (note that the following directory operations are performed at the bash prompt, I use shell> to represent, but also pay attention to the server on which the command is executed) shell> tar-cvf/tmp/mysql-snapshot.tar. then copy the archive file to the/tmp directory of the slave server. On the slave server, enter the database data directory and run the following command to decompress the archive file: shell> tar-xvf/tmp/mysql-snapshot.tar when flush tables with read lock is set to read lock effective (that's why I just said not to exit the mysql prompt ), read the current binary log name and offset value on the MASTER server, and directly enter the command show master status. mysql> show master status; + ------------------------ + ------------ + ------------------- + region + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + region + ------------ + ------------------ -+ ---------------------- + | Mysql-bin.000045 | 947 | + ---------------------------- + ------------ + ------------------------------- + File column displays the log name, while Position displays the offset. In this example, the binary log value is a mysql-bin.000045 offset of 947. Record this value. These values will be used for setting slave servers later. They represent the replication coordinates, which tell the slave server to update from the master server. After obtaining the snapshot and recording the log name and offset, you can re-enable the Write activity on the master server: mysql> unlock tables; 4. make sure that my. the [mysqld] section of the cnf file contains a log-bin option. This part should also have a server-id = Master_id option, where master_id must be a positive integer between 1 and 232-1. For example: [mysqld] log-bin = mysql-bin server-id = 1 you usually open my. after cnf, the default setting is as follows: # Replication Master Server (default) # binary logging is required for replicationlog-bin = mysql-bin # required unique id between 1 and 2 ^ 32-1 # defaults to 1 if master-host is not set # but will not function as a master if omittedserver-id = 1 if these options do not exist, add them and restart the server. if binary logging (binary log) is not enabled, the server cannot be called a replication master server. stop slave server s Hell> mysqladmin-u root shutdown-p its my. add the following line to the cnf file: the value of [mysqld] server-id = slave_idslave_id is the same as that of Master_id. It must be a positive integer between 1 and 232-1. In addition, the slave server ID must be different from the master server ID. For example: [mysqld] server-id = 2 if multiple slave servers are set, each slave server must have a unique server-id value, it must be different from the master server and other slave servers. Note: The settings here are very different from those above. in fact, the default my. the corresponding settings have been set in cnf. As mentioned above, the master server does not need to be modified, and the modification to the slave server is also very simple. (In fact, the file is full and clear, so it should be okay if the file is good.) You must comment out the two options opened by the Master Server # Replication Master Server (default) # binary logging is required for replication # log-bin = mysql-bin # required unique id between 1 and 2 ^ 32-1 # defaults to 1 if master-host is not set # will not function as a master if omitted # server-id = 1 then open the corresponding option of Slave server # Replication Slave (commen T out master section to use this )...... (Omitted) # required unique id between 2 and 2 ^ 32-1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omittedserver-id = 2, 4 ...) 6. If you perform a binary backup of the data on the master server, copy the data from the slave server to the data directory on the slave server before starting the slave server. Ensure that the permissions on these files and directories are correct. The system account you use to run the slave server must be able to read and write these files, just as on the master server. 7. Start the slave server. Mysqld_safe -- user = mysql & the configuration of different servers may be different, usually in/etc/rc. d/rc. write in local. if you have already copied the server, use the -- skip-slave-start option to start the slave server so that it does not try to connect to the master server immediately. You may also want to use the -- logs-warnings option to start the slave server (enabled by default) to display more problem-related information in the error log (for example, network or connection problems ). Dropped connections are not recorded in the error log unless the option value is greater than 1. 8. if you use mysqldump to back up data on the master server, load the dump file to the slave server: shell> mysql-u root-p <dump_file. SQL. I didn't use mysqldump to back up data, so skip this step. 9. run the following statement on the slave server: mysql> change master to-> MASTER_HOST = '2017. 168.0.111 ', // write the name of the master server or IP address-> MASTER_USER = 'root', // write the account used for replication, previously, I used root-> MASTER_PASSWORD = 'xxx' directly, // copy the password of the account. Here is the root password-> MASTER_LOG_FILE = 'mysql-bin.000045 ', // The log name recorded earlier-> MASTER_LOG_POS = 947; // The offset returned from the previous log: Query OK, 0 rows affect The table below ed (0.01 sec) shows the maximum length of the string option: Master_Host60Master_USER16Master_PASSWORD32Master_Log_File255 10. START the SLAVE server thread: mysql> start slave; after these programs are executed, the SLAVE server should connect to the master server and supplement any updates that have occurred since the snapshot. Test: view mysql> show slave status \ G ******************************* 1 on the SLAVE server. row ************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.111Master _ User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000045Read_Master_Log_Pos: small: dbpi-relay-bin.000002Relay_Log_Pos: small: mysql-bin.000045Slave_IO_Running: small: Last_Errno: 0Last_Error: Skip_Counter: condition: condition: 352Until_Condition: condition: Until_Log_Pos: condition: Master_SSL_CA_Path: Master_SSL_Cert: condition: Master_SSL_Key: Seconds_Behind_Master: 0 red indicates that the two threads on the server have started. on the slave server, you can view the thread running status mysql> show processlist \ G ************************* ** 1. row ************************** Id: 3 User: root Host: localhost db: testCommand: Query Time: 0 State: NULL Info: show processlist **************************** 2. row ************************** Id: 18 User: system user Host: db: NULLCommand: Connect Time: 43 State: Waiting for master to send event Info: NULL **************************** 3. row ************************** Id: 19 User: system user Host: db: NULLCommand: Connect Time: 4294966771 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL3 rows in set (0.00 sec) make an update Statement on the master server, and the slave server will be updated at the same time. configuration complete. note: Because the slave server updates itself by reading the binary logs of the master server, all database modification operations must be performed on the master server, the slave server is only used for query. (that is, read-only database operations without writing ).