MYSQL遇到问题汇总

主从同步问题

SQL线程错误

Slave_IO_Running: Yes
Slave_SQL_Running: No

***************** 1. row *****************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 414990
Relay_Log_File: PS03-relay-bin.000002
Relay_Log_Pos: 397378
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 1008
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘18c01a46-df39-11e7-b98f-000c2989d7c7:1084’ at master log mysql-bin.000001, end_log_pos 414990. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 414837
Relay_Log_Space: 397777
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘18c01a46-df39-11e7-b98f-000c2989d7c7:1084’ at master log mysql-bin.000001, end_log_pos 414990. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 152
Master_UUID: 18c01a46-df39-11e7-b98f-000c2989d7c7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180131 11:14:13
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 18c01a46-df39-11e7-b98f-000c2989d7c7:1:50-1084
Executed_Gtid_Set: 18c01a46-df39-11e7-b98f-000c2989d7c7:1-1083,
97347859-dfe0-11e7-90cf-000c2911d0be:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

在5.6版本之前，没有使用GTID复制：
> stop slave;
> set global sql_slave_skip_counter = 1;
> start slave;
使用GTID复制:
通过mysqlbinlog找到错误GTID
> stop slave;
> set session gtid_next = '18c01a46-df39-11e7-b98f-000c2989d7c7:1084';
> begin;
> commit;
> set session GTID_NEXT = AUTOMATIC;
> start slave;

MYSQL useSSL

web应用连接mysql时，日志出现提示
Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

原因是MYSQL高版本需要指明是否需要使用SSL连接：
可以在程序连接数据库中指定：
如： url=jdbc:mysql://localhost:3306/database?characterEncoding=utf8&useSSL=false
如果使用useSSL=true,则mysql需要配置过ssl，查看
> show variables like '%ssl%'

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | DISABLED        |
| have_ssl      | DISABLED        |
| ssl_ca        |                 |
| ssl_capath    |                 |
| ssl_cert      |                 |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       |                 |
+---------------+-----------------+
9 rows in set (0.03 sec)

没有配置，则需要配置开启SSL
# cd /usr/local/mysql
# service mysqld stop
# bin/mysql_ssl_rsa_setup 在mysql data目录中，会产生.pem文件
授权data目录下新文件权限
# chown -R mysql.mysql data
# service mysqld start
再次查看SSL
> show variables like '%ssl%';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.02 sec)