MysqlAB replication setup and troubleshooting

MySQL Master-Slave replication (Master-Slave) is also called AB replication. Mysql is the most widely used free database in the world ..

MySQL Master-Slave replication (Master-Slave) is also called AB replication. Mysql is the most widely used free database in the world ..

Common Fault summary and Handling Methods

1. It may be/usr/local/mysql/data/***. The pid file has no write permission.
Solution: grant permissions, execute "chown-R mysql: mysql/var/data" "chmod-R 755/usr/local/mysql/data", and then restart mysqld!

2. the mysql process may already exist in the process.
Solution: Run "ps-ef | grep mysqld" to check whether the mysqld process exists. If the "kill-9 process number" is used to kill the process and restart mysqld!

3. It may be the second time that mysql is installed on the machine. Residual data affects service startup.
Solution: Go to the mysql data DIRECTORY/data to see, if there is a mysql-bin.index, just remove it, it is the culprit. I am using the third method to solve this problem !? P = 186

4. mysql uses/etc/my when the configuration file is not specified at startup. cnf configuration file. Open this file to check whether the data directory (datadir) is specified in [mysqld ).
Solution: Set this line in [mysqld]: datadir =/usr/local/mysql/data

5. skip-federated Field Problems
Solution: Check whether the skip-federated field has been commented out in the/etc/my. cnf file. If so, comment out the field immediately.
###
An error occurs mainly on the slave server.
The SLAVE service cannot be started after the master command is changed due to some incorrect operations or the MASTER is changed midway through. The system reports the following error:
Cocould not initialize master info structure; more error messages can be found in the MySQL error log.
The master info structure cannot be initialized; the MySQL error log records more detailed error information.
Two solutions:
First:
1. view the MySQL Error Log and view the cause.
For example, what is the last Position of synchronization.
In many cases, the service cannot be started because the synchronization identified by mysql always stays on the previous Position.
2. View master.info and relay-log.info
Master.info records MASTER information
Mysql-bin.000030
391156558
192.168.1.1
User_rep
Rep123
3306
60
0
Relay-log.info record current sync log information
235
Mysql-bin.000030
391156558
3. Stop the myslq service and delete master.info and relay-log.info
# Service mysql stop
/Data/datafile/# rm master.info
/Data/datafile/# rm relay-log.info
4. Start the mysql Service
# Service mysql start
5. Restart the MASTER and restart the SLAVE service.
The problem should be solved.
The second method is simple and commonly used.
Mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql> reset slave;
Query OK, 0 rows affected, (0.00 sec)
#############
Master-slave mysql is not synchronized first on Master
Show processlist; check whether there are too many Sleep processes. It is normal.
Show master status; also normal.
Run the Server Load balancer to view the details.
Show slave status;
Error message:
Error 'duplicate entry '1' for key 1' on query. default database: 'movivi1 '. query: 'insert INTO 'v1vid0 _ user_samename 'values (null, 1, '20170101', '11', '20160301', '20160301 ')'
Slave_ SQL _Running is NO
Seconds_Behind_Master is (null)
Obviously, Slave is not synchronized.
Solution:
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave;

########
1. network latency
Because mysql master-slave replication is an asynchronous replication based on binlog, The binlog file is transmitted over the network. Of course, the network latency is the majority of the reasons for master-slave synchronization, especially for cross-data-center data synchronization, this probability is very high. Therefore, read/write splitting should be performed and the preliminary design should be conducted from the business layer.
2. the load on the master and slave machines is inconsistent.
Since mysql master-slave replication starts one I/O thread on the master node, and one SQL thread and one I/O thread are started on the master node, the load on any machine is very high, so I am too busy, as a result, any thread may experience insufficient resources, and the master may be inconsistent.
3. Inconsistent settings of max_allowed_packet
The max_allowed_packet set on the master node is larger than the slave node. When a large SQL statement is executed on the master node, the master node cannot be executed because it is too small.
4. The primary key is inconsistent with the auto-increment step.
5. If sync_binlog = 1 or innodb_flush_log_at_trx_commit = 1 is not set, binlog or relaylog files may be damaged, resulting in inconsistent master data.
6. The master node is not synchronized due to a bug in mysql.
7. Version inconsistency, especially when the higher version is the primary version and the lower version is the slave version, the functions supported by the primary version are not supported.
The above are some situations where the master node is not synchronized. There may be some other non-synchronization situations. Please tell us the situations where you encounter different masters.
Based on the above situation, first ensure that max_allowed_packet, the auto-incrementing key start point is consistent with the Growth Point settings, and sacrifice some performance on the master node sync to enable _ binlog. For databases using innodb, we recommend that you configure the following content
1. innodb_flush_logs_at_trx_commit = 1
2. innodb-support_xa = 1 # Mysql 5.0 or above
3. innodb_safe_binlog # Mysql 4.0
We recommend that you add the following two parameters.
1. skip_slave_start
2. read_only
Restart the MASTER and restart the SLAVE service.

This article is from the "windy, cold" blog. Please keep this source