1. 数据库性能排查
查看执行情况:
# 查看正在执行的所有线程状态信息
SHOW PROCESSLIST;
# 查看完整查询语句
SHOW FULL PROCESSLIST;
# 查询当前运行的执行时间最久的sql
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' ORDER BY time DESC LIMIT 10;
# 当前的事务
SELECT * FROM information_schema.INNODB_TRX;
# 当前的锁
SELECT * FROM information_schema.INNODB_LOCKS;
# 当前的锁等待信息
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
2. 复制表数据
如果表的行数不多,直接用 insert select 语句复制并插入。
INSERT INTO TABLE2 (col1, col2, col3)
SELECT col1, col2, col3 FROM TABLE1 Where a > 100;
如果表比较大,可以用 mysqldump 导出表的全部或部份数据。
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF <database> <table> --where="a>900" --result-file=a.sql
然后再通过 sql 文件导入数据库。
SOURCE a.sql
也可以导出为 csv 文件。
select * from db1.t where a>900 into outfile 'a.csv';
然后导入 csv 文件。
load data infile 'a.csv' into table db2.t;
3. 删除表的所有数据
如果表的数据量不大,可以用 delete 语句直接删除。对于非常大的表,DELETE 会非常慢,因为这种方式会一行行地删除数据,并记录每行的更改到事务日志中。
DELETE FROM user;
使用 TRUNCATE 更快,因为它不记录每行的删除操作,它会删除表并重新创建回来,而不是一行行删除。这将会充值表的自增计数器,而且不能回滚。
TRUNCATE TABLE user;
DROP 将会删除表结构和数据。
DROP TABLE user;
对服务器影响更小的方式是分批每次删除一些数据,直至表不存在数据。这种方式每次占用的锁范围更小时间也更短,串行化执行不会对服务器占用很多资源,也不会影响到其他客户端的工作。
DELETE FROM user limit 100;
4. 主备同步
主库配置:
[mysqld]
server-id=1 # 主库的唯一 ID
log-bin=mysql-bin # 启用二进制日志
binlog-format=ROW # 推荐使用 ROW 格式
重启 MySQL 服务:
systemctl restart mysql
创建用于复制的用户:
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
查看主库状态,获得主库的 File 和 Position 值。
SHOW MASTER STATUS;
接着配置备库:
[mysqld]
server-id=2 # 备库的唯一 ID,必须与主库不同
relay-log=mysql-relay-bin # 启用中继日志
read-only=1 # 备库设置为只读模式(可选)
重启 MySQL 服务:
systemctl restart mysql
配置连接主库的信息:
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001', -- 主库的 File 值
MASTER_LOG_POS=123; -- 主库的 Position 值
启动备库复制:
START SLAVE;
检查复制状态:
SHOW SLAVE STATUS;
5. 主从不一致
问题排查:
# 主库查看
SHOW PROCESSLIST;
SHOW MASTER STATUS;
# 从库查看
SHOW SLAVE STATUS;
对主库锁表并查看同步点文件和位置:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
备份数据:
mysqldump -uroot -p -hlocalhost > mysql.sql
把备份文件传到从库。
停止从库:
STOP SLAVE;
从库导入数据备份:
SOURCE mysql.sql
设置从库同步,这里设置同步点文件和位置:
CHANG MASTER TO master_host='', master_user='',master_port=, master_password='', master_log_file='', master_log_pos=;
重新开启同步,查看同步状态:
START SLAVE;
SHOW SLAVE STATUS;
主库解除表锁定:
UNLOCK TABLES;