MySQL Common commands

1. View the MySQL lock table process.

1 View the lock table process.  2 SELECT * from information_schema. Innodb_trx\g3 trx_mysql_thread_id:1111145 kills the lock table process.  611111

2. Global read-only

1 #设置全局只读 2 Set Global read_only=1; 3 4 #取消全局只读 5 Set Global read_only=0;

3. Get the number of connections per IP in the database

1 #获取数据库每个ip的连接数2#/usr/local/mysql/bin/mysql-u root-h127.0.0.1-E"show Processlist\g;"| Egrep"host\:"| Awk-f:'{print $}'| Sort | Uniq-C3 4#/usr/local/mysql/bin/mysql-u root-h127.0.0.1--skip-column-names-e"show Processlist;"|awk'{print $}'|awk-f":" '{print $}'|sort|uniq-C5 6Mysql>SelectSubstring_index (Host,':',1) asIP, COUNT (*) fromInformation_schema.processlist GROUP by IP;

4. Manually delete the binary log

1 #手动删除binlog 2 ' master-bin.000480 ';    #删除master binary logs prior to-bin.000480

5. Global Read lock

1 #增加读锁 2 Lock ; 3 mysql> unlock tables; #释放读锁

6, create the database has UTF8 encoding format and import SQL file garbled

1 #创建数据库以utf8格式 2 CREATE DATABASE oma DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci; 3 4 #mysql Source command garbled

MySQL Common commands