一、MySQL常用命令:
1、查看文件安装路径和运行路径:
whereis mysql
which mysql
2、检查所有库、所有表
mysqlcheck -c --all-databases -uroot -pzhangtongle123
mysqlcheck 检查、修复,优化表:http://www.php.cn/mysql-tutorials-357648.html
3、检查mysql服务状态。
mysqladmin -u root -pzhangtongle123 -h localhost ping
4、获取mysql在当前系统上的几个状态值
mysqladmin -u root -pzhangtongle123 -h localhost status
5、获取数据库当前的连接信息
mysqladmin -u root -pzhangtongle123 -h localhost processlist
6、获取当前数据库的连接数
mysql -uroot -pzhangtongle123 -BNe "show variables like '%max_connections%';" information_schema
mysql -u root -pzhangtongle123 -BNe "select host,count(host) from processlist group by host;" information_schema
7、显示mysql的运行时间
mysql -uroot -pzhangtongle123 -NBe"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'
8、查看数据库的大小
mysql -u root -pzhangtongle123 -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;'
9、查看某个表的字段信息
mysql -uroot --password=zhangtongle123 -e "SHOW COLUMNS FROM employee" mydb3 | awk '{print $1}' | tr "\n" "," | sed 's/,$//g'
10、查看mysql的进程
ps -ef | grep "mysqld_safe" | grep -v "grep"
ps -ef | grep "mysqld" | grep -v "mysqld_safe"| grep -v "grep"
11、 查看当前数据库的详细状态
mysql -u root -pzhangtongle123 -e 'show status'
http://www.2cto.com/database/201302/190734.html
http://blog.csdn.net/qiuyepiaoling/article/details/6004611
12、TPS、QPS,Python监控脚本
13、主从复制设置命令
创建一个主从复制账号:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'%' IDENTIFIED BY '123456';
主库上执行:
show Master status;(先在从库上取出:MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=107)
从库上执行:
stop slave;
reset slave all;
CHANGE MASTER TO MASTER_HOST='192.168.1.60',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=107;
start slave;
show slave status;
14、解决主从不一致:
14.1、跳过错误
slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;
14.2、检查主从数据是否一致
pt-table-checksum h='192.168.1.60',u='root',p='zhangtongle123',P=3306 --databases "mydb3" --tables "employee" --nocheck-replication-filters --replicate=mydb3.checksums --no-check-binlog-format
说明:(DIFFS等于1代表主从数据不一致)
14.3、确定不一致的从库地址以及库表:
pt-table-checksum h='192.168.1.60',u='root',p='zhangtongle123',P=3306 --databases "mydb3" --tables "employee" --nocheck-replication-filters --replicate=mydb3.checksums --no-check-binlog-format --replicate-check-only
14.4、查看详细的不一致数据:
pt-table-sync --replicate=mydb3.checksums h='192.168.1.60',u='root',p='zhangtongle123',P='3306' --databases=mydb3 --tables=employee --print
14.5、删除从库与主库上不一致的数据:
pt-table-sync --replicate=mydb3.checksums h='192.168.1.60',u='root',p='zhangtongle123',P='3306' --databases=mydb3 --tables=employee --execute