MySQL操作汇总

管理

修改密码

set password for ‘root’@’localhost’=password(‘xxxxxxx’);

批量删除正在执行的任务

mysql -s -e “show processlist;” | grep ‘Sending data’ | awk ‘{print “kill “$1”;”}’

备份

mysqldump在做全库备份的时候，会进行锁库的操作，所以在生产环境下很难进行停服务的操作。可以利用xtrabackup工具，支持热备份.

备份：

innobackupex –user=root –password=root –defaults-file=/**etc/mysql/my.cnf **/**data/mysql_backup/**full_backup

复制Replication

配置

mysql> show slave status\G

​ Slave_IO_State: Waiting for master to send event

​ Relay_Master_Log_File: mysql-bin.000190

​ Slave_IO_Running: Yes

​ Slave_SQL_Running: No

​ mysql> slave stop;

Query OK, 0 rows affected (0.01 sec)

mysql> change master to Master_Log_File=’mysql-bin.000190’, Master_Log_Pos = 155311866;

Query OK, 0 rows affected (2.23 sec)

Master_Log_File:取Relay_Master_Log_File的值，relay的时候出错。 Master_Log_Pos:取Exec_Master_Log_Pos的值，relay的时候读取masterlogfile的位置

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)