99.MySQL故障处理/常用运维命令

DustinZ柯基君
248 阅读18分钟

故障处理

1.多实例部署和免密码登录

1.操作系统版本：Centos7.2  
2.数据库IP地址：localhost   
3.数据库实例端口:3306,3307,3308    
4.数据库版本：mysql 5.7.21  


初始化数据库实例  
注意每个.cnf文件中的目录和端口,mysql程序文件可以共同使用，数据文件使用各自的目录。使用不生成随机密码方式进行初始化。  
mysqld --defaults-file=/etc/my3306.cnf --initialize-insecure --user=mysql --basedir=/home/mysql/product/5.7/mysql-1 --datadir=/app/mysql/my3306;  
mysqld --defaults-file=/etc/my3307.cnf --initialize-insecure --user=mysql --basedir=/home/mysql/product/5.7/mysql-1 --datadir=/app/mysql/my3307;  
mysqld --defaults-file=/etc/my3308.cnf --initialize-insecure --user=mysql --basedir=/home/mysql/product/5.7/mysql-1 --datadir=/app/mysql/my3308;  

启动数据库实例  
/home/mysql/product/5.7/mysql-1/bin/mysqld_safe --defaults-file=/etc/my3306.cnf &   
/home/mysql/product/5.7/mysql-1/bin/mysqld_safe --defaults-file=/etc/my3307.cnf &  
/home/mysql/product/5.7/mysql-1/bin/mysqld_safe --defaults-file=/etc/my3308.cnf &  

停止数据库实例  
mysqladmin --defaults-file=/etc/my3306.cnf -uroot -p123456 shutdown  
mysqladmin --defaults-file=/etc/my3307.cnf -uroot -p123456 shutdown  
mysqladmin --defaults-file=/etc/my3308.cnf -uroot -p123456 shutdown  

连接数据库实例 (无密码，所以直接回车进入mysql命令行)  
mysql -p --socket=/home/mysql/run/mysql3306.sock
使用临时密码：alter user 'root'@'localhost' identified by '123456';flush privileges;  
mysql -p --socket=/home/mysql/run/mysql3307.sock  
使用临时密码：alter user 'root'@'localhost' identified by '123456';flush privileges;  
mysql -p --socket=/home/mysql/run/mysql3308.sock 
使用临时密码：alter user 'root'@'localhost' identified by '123456';flush privileges;
  
重新连接数据库  
mysql -uroot -p123456 --socket=/home/mysql/run/mysql3306.sock   
mysql -uroot -p123456 --socket=/home/mysql/run/mysql3307.sock   
mysql -uroot -p123456 --socket=/home/mysql/run/mysql3308.sock   

使用mysql_config_editor免密码登录(回车后直接输入密码即可)，只能有user,password,host,socket,port这几个属性。  
mysql_config_editor set --login-path=3306 --host=localhost --port=3306 --socket=/home/mysql/run/mysql3306.sock --user=root --password  
mysql_config_editor set --login-path=3307 --host=localhost --port=3307 --socket=/home/mysql/run/mysql3307.sock --user=root --password  
mysql_config_editor set --login-path=3307 --host=localhost --port=3308 --socket=/home/mysql/run/mysql3308.sock --user=root --password  

查看当前设置的login-path,login-path可以设置不同的名字。如果3个实例的密码都一样。可以使用一个login-path进行登录操作。
mysql_config_editor print --all  
[3306]  
user = root  
password = *****  
host = localhost  
socket = /home/mysql/run/mysql.sock  
port = 3306  
[3307]  
user = root  
password = *****  
host = localhost  
socket = /home/mysql/run/mysql3307.sock  
port = 3307  
[3308]
user = root  
password = *****  
host = localhost  
socket = /home/mysql/run/mysql3308.sock  
port = 3308  

设置完成后使用如下命令登录即可。  
mysql --login-path=3307  


使用alias别名来设置不同端口的启动，关闭和登录。  
启动  
echo "alias mysql.3307.up='mysqld_safe --defaults-file=/etc/my3307.cnf &'" >> /root/.bash_profile  
echo "alias mysql.3306.up='mysqld_safe --defaults-file=/etc/my3306.cnf &'" >> /root/.bash_profile  
echo "alias mysql.3308.up='mysqld_safe --defaults-file=/etc/my3308.cnf &'" >> /root/.bash_profile  

关闭  
echo "alias mysql.3306.down='mysqladmin --defaults-file=/etc/my3306.cnf --login-path=3306 shutdown'" >> /root/.bash_profile  
echo "alias mysql.3307.down='mysqladmin --defaults-file=/etc/my3307.cnf --login-path=3307 shutdown'" >> /  root/.bash_profile  
echo "alias mysql.3308.down='mysqladmin --defaults-file=/etc/my3308.cnf --login-path=3308 shutdown'" >> /  root/.bash_profile  

登录
echo "alias mysql.3306.login='mysql --defaults-file=/etc/my3306.cnf --login-path=3306'" >> /root/.bash_profile  
echo "alias mysql.3307.login='mysql --defaults-file=/etc/my3307.cnf --login-path=3307'" >> /root/.bash_profile  
echo "alias mysql.3308.login='mysql --defaults-file=/etc/my3308.cnf --login-path=3308'" >> /root/.bash_profile  

source /root/.bash_profile  

结果
cat /root/.bash_profile  
# .bash_profile  
alias mysql.3306.login='mysql --defaults-file=/etc/my.cnf --login-path=3306'  
alias mysql.3306.down='mysqladmin --defaults-file=/etc/my.cnf --login-path=3306 shutdown' 
alias mysql.3306.up='mysqld_safe --defaults-file=/etc/my.cnf &'  

alias mysql.3307.login='mysql --defaults-file=/etc/my3307.cnf --login-path=3307'  
alias mysql.3307.down='mysqladmin --defaults-file=/etc/my3307.cnf --login-path=3307 shutdown'  
alias mysql.3307.up='mysqld_safe --defaults-file=/etc/my3307.cnf &'  

alias mysql.3308.down='mysqladmin --defaults-file=/etc/my3308.cnf --login-path=3308 shutdown'  
alias mysql.3308.up='mysqld_safe --defaults-file=/etc/my3308.cnf &'  
alias mysql.3308.login='mysql --defaults-file=/etc/my3308.cnf --login-path=3308'  

使用
mysql.3306.login  
mysql.3306.up  
mysql.3306.down  
来分别执行登录，启动，关闭操作更便捷。

2.传统复制转换为GTID复制的转换

1.操作系统版本：Centos7.2  
2.数据库IP地址：192.168.1.1/192.168.1.2
3.数据库实例端口:3306  
4.数据库版本：mysql 5.7.21  

观察数据库gtid的状态，为OFF是未开启的状态。  
mysql>show variables like '%gtid%';  
+----------------------------------+-----------+  
| Variable_name                    | Value     |  
+----------------------------------+-----------+  
| binlog_gtid_simple_recovery      | ON        |  
| enforce_gtid_consistency         | OFF       |  
| gtid_executed_compression_period | 1000      |  
| gtid_mode                        | OFF       |  
| gtid_next                        | AUTOMATIC |    
| gtid_owned                       |           |  
| gtid_purged                      |           |  
| session_track_gtids              | OFF       |  
+----------------------------------+-----------+  
8 rows in set (0.00 sec)

步骤1：
执行设置允许所有事务可以违反GTID的一致性，设置完成后观察错误日志是否有错误。无错误下一步。 (需master和slave同时执行命令)     
mysql>set @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;  
Query OK, 0 rows affected (0.00 sec)  
日志信息
2020-04-07T02:03:54.169866Z 330234 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.  

步骤2：
执行设置所有的事务都不可以违反GTID的一致性，设置完成后观察错误日志是否有错误。无错误下一步。 (需master和slave同时执行命令)   
mysql>set @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;  
Query OK, 0 rows affected (0.00 sec)  
2020-04-07T02:04:10.679896Z 330234 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON.  

步骤3：
执行设置GTID_MODE = OFF_PERMISSIVE，表示新的事务是匿名的允许复制的事务是匿名的或者GTID的。无错误下一步。 (需master和slave同时执行命令)     
mysql>set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;  
Query OK, 0 rows affected (0.00 sec)  
2020-04-07T02:04:26.031833Z 330234 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE.  

步骤4：
执行设置GTID_MODE = ON_PERMISSIVE，表示新事务使用GTID同时允许复制的事务是匿名事物或GTID的。无错误下一步。 (需master和slave同时执行命令)   
mysql>set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;  
Query OK, 0 rows affected (0.00 sec)  

步骤5：
只在从库上执行。等待Ongoing_anonymous_transaction_count的值为0，必须为0才能执行下一步。表示当前匿名的正在进行的事务数量。0表示无需等待。  
mysql>show status like 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';  
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.00 sec)

步骤6：
需要主从都执行设置GTID_MODE = ON，开启GTID
mysql> set @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.00 sec)

查看修改后GTID的状态。
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)  

mysql> show slave status \G  
*************************** 1. row ***************************    
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 192.168.1.2  
                  Master_User: slave  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: xxxxxxxx  
          Read_Master_Log_Pos: xxxxxxxx 
               Relay_Log_File: xxxxxxxx
                Relay_Log_Pos: 5202  
        Relay_Master_Log_File: xxxxxxxx
             Slave_IO_Running: Yes  
            Slave_SQL_Running: Yes  
                 Skip_Counter: 0  
          Exec_Master_Log_Pos: xxxxxxxx 
             Relay_Log_Space: xxxxxxxx  
        Seconds_Behind_Master: 0  
           Retrieved_Gtid_Set:xxxxxxxx:1-11  
            Executed_Gtid_Set:xxxxxxxx:1-978,xxxxxxxx:1-11  
                Auto_Position: 0  
         Replicate_Rewrite_DB:   
                 Channel_Name:   
           Master_TLS_Version:   
1 row in set (0.00 sec)
停止当前复制，将复制调整为GTID的复制模式。
mysql> stop slave; change master to master_auto_position=1;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: xxxxxxxx
          Read_Master_Log_Pos: xxxxxxxx
               Relay_Log_File: xxxxxxxx
                Relay_Log_Pos: xxxxxxxx
        Relay_Master_Log_File: xxxxxxxx
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: xxxxxxxx
              Relay_Log_Space: 7930
        Seconds_Behind_Master: 0
             Master_Server_Id: 3
                  Master_UUID: xxxxxxxx
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
           Retrieved_Gtid_Set: xxxxxxxx:22-33
            Executed_Gtid_Set: xxxxxxxx:1-2823,xxxxxxxx:1-33
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

修改my.cnf。添加GTID参数，下次重启后可以直接生效。
vi /etc/my.cnf
gtid_mode=on
enforce-gtid-consistency=1

主库更改后日志
2020-04-07T02:03:54.169866Z 330234 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
2020-04-07T02:04:10.679896Z 330234 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON.
2020-04-07T02:04:26.031833Z 330234 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE.
2020-04-07T02:04:39.055807Z 330234 [Note] Changed GTID_MODE from OFF_PERMISSIVE to ON_PERMISSIVE.
2020-04-07T02:05:06.166900Z 330234 [Note] Changed GTID_MODE from ON_PERMISSIVE to ON.
2020-04-07T02:06:44.144786Z 814 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2020-04-07T02:06:44.147088Z 813 [Note] Slave I/O thread killed while reading event for channel ''
2020-04-07T02:06:44.147109Z 813 [Note] Slave I/O thread exiting for channel '', read up to log 'xxxxxxxx', position xxxxxxxx
2020-04-07T02:06:46.295996Z 330630 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2020-04-07T02:06:46.296623Z 330631 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'xxxxxxxx' at position xxxxxxxx, relay log './xxxxxxxx' position: xxxxxxxx
2020-04-07T02:06:46.296705Z 330630 [Note] Slave I/O thread for channel '': connected to master 'slave@xxxxxxxx:3306',replication started in log 'xxxxxxxx' at position xxxxxxxx
2020-04-07T02:07:20.802957Z 99 [Note] Aborted connection 99 to db: 'unconnected' user: 'slave' host: 'xxxxxxxx' (failed on flush_net())
2020-04-07T02:07:29.051153Z 330650 [Note] Start binlog_dump to master_thread_id(330650) slave_server(3), pos(, 4)

从库更改后日志。
2020-04-07T10:03:56.529399+08:00 861709 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
2020-04-07T10:04:13.189570+08:00 861709 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON.
2020-04-07T10:04:27.901550+08:00 861709 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE.
2020-04-07T10:04:40.980289+08:00 861709 [Note] Changed GTID_MODE from OFF_PERMISSIVE to ON_PERMISSIVE.
2020-04-07T10:05:07.092514+08:00 861709 [Note] Changed GTID_MODE from ON_PERMISSIVE to ON.
2020-04-07T10:05:36.076381+08:00 103 [Note] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 94643201; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 90459; waited at clock conflicts = 549253984500 waited (count) when Workers occupied = 293583 waited when Workers occupied = 0
2020-04-07T10:06:44.312319+08:00 4182 [Note] Aborted connection 4182 to db: 'unconnected' user: 'slave' host: 'xxxxxxxx' (failed on flush_net())
2020-04-07T10:06:46.296195+08:00 862759 [Note] Start binlog_dump to master_thread_id(862759) slave_server(2), pos(, 4)
2020-04-07T10:07:20.515752+08:00 103 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2020-04-07T10:07:20.517898+08:00 102 [Note] Slave I/O thread killed while reading event for channel ''
2020-04-07T10:07:20.517917+08:00 102 [Note] Slave I/O thread exiting for channel '', read up to log 'xxxxxxxx', position xxxxxxxx
2020-04-07T10:07:29.036590+08:00 862805 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2020-04-07T10:07:29.037617+08:00 862805 [Note] Slave I/O thread for channel '': connected to master 'slave@xxxxxxxx',replication started in log 'xxxxxxxx' at position xxxxxxxx
2020-04-07T10:07:29.046672+08:00 862806 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.001033' at position 20930470, relay log '.xxxxxxxx' position: 4




线上GTID复制转换为传统的复制(数据库版本必须为mysql5.7)

步骤1：
从库停止复制，如果是主主复制，则两遍都要停止复制进程。
stop slave;

步骤2：
假设关闭复制后，当前的复制位置为'mysql-bin.000015', position 67执行如下命令，将master_auto_position设置为0
change master to master_auto_position=0,master_log_file='mysql-bin.000015', master_log_pos=67;

步骤3：  
需要主从都执行设置GTID_MODE = ON_PERMISSIVE，表示新事务使用GTID同时允许复制的事务是匿名事物或GTID的。无错误下一步。 (需master和slave同时执行命令)   
mysql>set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

步骤4：
需要主从都执行设置GTID_MODE = OFF_PERMISSIVE，表示新的事务是匿名的允许复制的事务是匿名的或者GTID的。无错误下一步。 (需master和slave同时执行命令)   
mysql>set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
2020-04-07T02:04:26.031833Z 330234 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE.

步骤5：
等待所有数据库实例@@global.gtid_owned为空，表示正在有线程执行的全局GTID集合。
等待所有的slave都复制完成匿名事务。
mysql> select @@global.gtid_owned;
+---------------------+
| @@global.gtid_owned |
+---------------------+
|                     |
+---------------------+
1 row in set (0.01 sec)

步骤6：
执行设置GTID_MODE = OFF，关闭GTID (需master和slave同时执行命令)   
mysql> set @@GLOBAL.GTID_MODE = OFF;
Query OK, 0 rows affected (0.00 sec)
修改配置文件，删除GTID参数，在重启后也会生效。

3.跳过传统复制错误和GTID的错误

数据库版本：5.7.21
主从模式：根据binlog日志复制，主主模式。并开启并行复制
自动增长：1.1增长为1 3 5 / 1.2增长为2 4 6 并且同步
IP地址：192.168.1.1和192.168.1.2
VIP地址：192.168.1.101（读写），192.168.1.102（读）

故障描述：
在监控界面发现192.168.1.1的主从失败告警信息。
登录服务器后。检查192.168.1.1的主从状态。因为已经处理完毕。所以没有当时的错误信息截图。
[ERROR] Slave SQL for channel '': worker thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable. Error_code: 12052018-12-22T14:11:40.025529Z 6 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000012, end_log_pos 10159; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000012' position 9515

经过错误提示，
select * from performance_schema.replication_applier_status_by_worker\G 这个表。
发现是第一个并行线程哪里出的错。提示的也是mysql-bin.000012和position 9515
经过检查发现是在mysql-bin.000012上的position 9515上处理错误。所以在192.168.1.2上查看binlog日志的内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /app/mysql/data/mysql-bin.000012 > 1.txt
不加--no-defaults 会不识别my.cnf中的character-set-server=utf8参数。

发现是更新 userdb中的t_s_base_user这张表中id为250790的数据。
经过检查。在1和2上都有这一条记录。
因为主主复制的时候。数据还没有复制过来。所以在1上并不存在这一条记录。
因为有两个VIP地址。2这个地址又没有添加只读的操作。所以可能在2上做了更新。1上不存在。所以造成了错误。
所以在1上跳过了错误。跳过完成后主从复制恢复。
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;


### UPDATE `adb`.`t_s_base_user`### WHERE###   
@1=250790###   
@2=NULL###   
@3=NULL###   
@4='6730fb'###  

SET###   
@1=250790###   
@2=NULL###   
@3=NULL###   
@4='a4e162'###   

UPDATE `fdb`.`t_s_base_user`### WHERE###   
@1=250790###   
@2=NULL###   
@3=NULL###   
@4='6730fb'###   

SET###   
@1=250790###   
@2=NULL###   
@3=NULL###   
@4='a4e162'###   


跳过GTID错误
查看主从复制状态
show slave status \G
根据错误信息可以查看到错误的代码比如1062.错误的binlog文件和end_log_pos值。可以根据exec_master_logs_pos查看开始值。
这样执行错误的事务的开始点和结束点都找到了。

在主库上作分析看是什么语句，假如是插入一条主键错误的语句，应该在从库中查询一下是否有这个记录，如果有的话。可以跳过处理。
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v --start-position= --stop-position= mysql-bin.000012 start > 1.txt

当前的gtid,1-8为冲突的事务，要注意恶gtid会有一长串。并不是一条
retrieved_gtid_set 假如为uuid:1-8
executed_gtid_set  假设为uuid:1-7

跳过错误
stop slave;
指定出错误的事务
set gtid_next='uuid:8'
开启空事务并且提交
begin;commit;
指定自动
set gid_next=='AUTOMATIC'
启动复制
start slave;
查看状态
show slave status \G
需要谨慎的使用。

4.数据库添加索引冲突1061错误

1.操作系统版本：Centos6.8
2.数据库IP地址：67/68
3.数据库实例端口:3306
4.数据库版本：mysql 5.7.11
5.复制关系： 主主复制

业务报错报错，主从复制状态停止。
登录数据库67和68后。
show slave status \G
两边都报错误1061 
[ERROR] Slave SQL for channel '': Worker 0 failed executing transaction 'be25d209-845b-11e9-a5d3-005056ab533d:54394786' at master log slave-bin.004465, end_log_pos 168316564; 
Error 'Duplicate key name 'Idx_create_time'' on query. Default database: 'xxxxx'. 
Query: 'ALTER table xxxxxx add INDEX Idx_create_time(create_time)', Error_code: 1061
'xxxxx'. Query: 'ALTER table xxxxx add INDEX Idx_create_time(create_time)'
业务在对表添加索引的时候没有查看表结构，已经存在一个索引叫Idx_create_time(create_time)
解决：
stop slave;
alter table xxxxx INDEX Idx_create_time(create_time);
start slave;
show slave status \G 恢复正常

5.多主复制(主主复制)

1.操作系统版本：Centos7.2
2.数据库IP地址：192.168.1.5/6/11/12  192.168.2.34/35/36/37
3.数据库实例端口:3307,3308
4.数据库版本：mysql 5.7.21
5.主从复制关系5-6 主主复制  11-12 主主复制

需求：
1.迁移192.168.1.11/12 3306端口的adb 到192.168.1.5/6 3307端口
2.迁移192.168.2.34/35 3306端口的bdb 到192.168.1.5/6 3307端口
3.迁移192.168.2.36/37 3306端口的cdb/ddb 到192.168.1.5/6 3308端口


迁移adb到3307
登录192.168.1.11执行
mysqldump --master-data=2 --set-gtid-purged=off --single-transaction --triggers --routines --events --all-databases > 13.sql
备份完成后，传输至192.168.1.5/6
192.168.1.5/6执行
mysql -uroot -pxxxxxx -P3307
source 13.sql
导入成功后，通过more 13.sql查看当前master的binlog和positions位置。搭建主从复制。
再192.168.1.5/6 3307端口执行,必须指定binlog和positions来搭建。不可以用auto_position=1来搭建会报错。
reset master;
change master to
master_host ='192.168.1.11',
master_port= 3306,
master_user= 'xxxxxx',
master_password= 'xxxxxx',
MASTER_LOG_FILE='xxxxxxxxx', 
MASTER_LOG_POS=150;
搭建完成后。
如果切换的时候，不再复制192.168.1.11的数据源。而是192.168.1.5/6互为主从复制。则执行
在192.168.1.5上执行
stop xxxxxx;
change master to master_host ='192.168.1.6',master_port= 3307,master_auto_position=1;
start xxxxxx;
show xxxxxx status \G
在192.168.1.6上执行
stop xxxxxx;
change master to master_host ='192.168.1.5',master_port= 3307,master_auto_position=1;
start xxxxxx;
show xxxxxx status \G
则互为主备。切换成功。


迁移bdb到3307
登录192.168.2.34执行
mysqldump --master-data=2 --set-gtid-purged=off --single-transaction --triggers --routines --events bdb > 34.sql
备份完成后，传输至192.168.1.5/6
192.168.1.5/6执行
mysql -uroot -pxxxxxx -P3307
create database bdb
use bdb
source 34.sql
导入成功后，通过more 34.sql查看当前master的binlog和positions位置。搭建主从复制。
for channel为多主复制使用。通过多主复制来实现多个主的数据一致性。
再192.168.1.5/6 3307端口执行,必须指定binlog和positions来搭建。不可以用auto_position=1来搭建会报错。
reset master;
change master to
master_host ='192.168.2.34',
master_port= 3306,
master_user= 'xxxxxx',
master_password= 'xxxxxx',
MASTER_LOG_FILE='master-binlog.000003',
MASTER_LOG_POS=75921290 FOR CHANNEL 'd34';
start xxxxxx;
show xxxxxx status \G
搭建完成后。
如果切换的时候，不再复制10.112.187.34的数据源。而是192.168.1.5/6互为主从复制。则执行
在192.168.1.5 3307端口上执行
stop xxxxxx;
change master to master_host ='192.168.1.6',master_port= 3307,master_auto_position=1;
start xxxxxx;
show xxxxxx status \G
在192.168.1.6 3307端口上执行
stop xxxxxx;
change master to master_host ='192.168.1.5',master_port= 3307,master_auto_position=1;
start xxxxxx;
show xxxxxx status \G
则互为主备。切换成功。

登录192.168.2.36执行
mysqldump --master-data=2 --set-gtid-purged=off --single-transaction --triggers --routines --events --all-databases > 36.sql
备份完成后，传输至192.168.1.5/6
192.168.1.5/6执行
mysql -uroot -pxxxxxx -P3308
source 36.sql
导入成功后，通过more 36.sql查看当前master的binlog和positions位置。搭建主从复制。
再192.168.1.5/6 3308端口执行,必须指定binlog和positions来搭建。不可以用auto_position=1来搭建会报错。
reset master;
change master to
master_host ='192.168.2.36',
master_port= 3306,
master_user= 'xxxxxx',
master_password= 'xxxxxx',
MASTER_LOG_FILE='master-binlog.000049', 
MASTER_LOG_POS=676539283;
start xxxxxx;
show xxxxxx status \G
搭建完成后。
如果切换的时候，不再复制192.168.2.36的数据源。而是192.168.1.5/6互为主从复制。则执行
在192.168.1.5 3308上执行
stop xxxxxx;
change master to master_host ='192.168.1.6',master_port= 3308,master_auto_position=1;
start xxxxxx;
show xxxxxx status \G
在192.168.1.6 3308上执行
stop xxxxxx;
change master to master_host ='192.168.1.5',master_port= 3308,master_auto_position=1;
start xxxxxx;
show xxxxxx status \G
则互为主备。切换成功。

6.无法启动报错Read-only file system

收到数据库报警，报警信息如下。因安全问题，数据库地址和端口做模糊处理。
实例类型: MYSQL
物理地址: 10.1.1.1(为了安全模糊处理)
监听端口: 4(为了安全模糊处理)
实例名 : MYSQL
告警信息: PORTTEST-PORTTEST-{['Error', \"Can't connect to MySQL server on '10.1.1.1']"}

实例类型: MYSQL
物理地址: 10.1.1.1(为了安全模糊处理)
监听端口: 5(为了安全模糊处理)
实例名 : MYSQL
告警信息: PORTTEST-PORTTEST-{['Error', \"Can't connect to MySQL server on '10.1.1.1']"}

实例类型: MYSQL
物理地址: 10.1.1.1(为了安全模糊处理)
监听端口: 6(为了安全模糊处理)
实例名 : MYSQL
告警信息: PORTTEST-PORTTEST-{['Error', \"Can't connect to MySQL server on '10.1.1.1']"}

查看进程
此服务器共运行6个MySQL实例进程。
ps -ef|grep mysql 
发现4-6号端口的进程已经挂掉。mysqld_safe进程也没有。  
尝试启动4-6号端口的进程


mysqld_safe --defaults-file=4.cnf &
启动的同时，进程自动结束。

查看错误日志信息
XXXX mysqld[52940]: rm: cannot remove ‘/XX/XXX/XXXX/XXXX4.pid’: Read-only file system  
XXXX mysqld[52940]: XXXXX mysqld_safe Fatal error: Can't remove the pid file


2.错误分析
Read-only file system  只读文件系统
第一次遇到这样的错误。以前没有遇到过。
服务器使用SSD盘存放数据库数据。单独挂载出/data/目录存放数据。
通过百度的搜索和查询。指导我去查看/proc/mounts

通过查看mounts，其他挂载的目录信息都是rw，只有data目录是ro。所以造成数据库实例启动不起来。报Read-only file system错误。
cat /proc/mounts  
/dev/sda1 /boot xfs rw,relatime,attr2,inode64,noquota 0 0    
/dev/mapper/app /app xfs rw,relatime,attr2,inode64,noquota 0 0    
/dev/mapper/data /data xfs ro,relatime,attr2,inode64,noquota 0 0



3.SA处理


因为服务器的资源由SA管理。所以报给SA同事来处理。  
经过SA同事的排查，回复我磁盘系统损坏，需要修理。  
修理之前先修复一下文件系统和磁盘，让我在试一下。我尝试启动了数据库后，虽然可以正常启动，但是过来几分钟之后数据库进程还是会挂掉。   
反馈给SA同事后，SA反馈给原厂来修理和替换磁盘。维修成功后，重新部署了MySQL数据库实例，服务器运行正常。

常用运维命令

1.查看表行数，索引大小，碎片大小(truncate表示截取位数，保留2位小数)

select   
TABLE_NAME as tablename,  
TABLE_ROWS as tablerows,  
concat(truncate(DATA_LENGTH/1024/1024/1024,2),'G') as data_size,  
concat(truncate(INDEX_LENGTH/1024/1024/1024,2),'G')as index_size,  
concat(truncate(DATA_FREE/1024/1024/1024,2),'G') as data_free  
from information_schema.tables   
where table_schema='lmis' order by table_rows;

2.统计当前连接数据库的ip地址和数量(SUBSTRING_INDEX截取字符串，:为分隔符，1为分割后的部分)

select 
SUBSTRING_INDEX(host,':',1),
count(*) 
from information_schema.processlist 
where DB='lmis' 
group by SUBSTRING_INDEX(host,':',1) order by count(*) desc;
avatar
文章
阅读
粉丝