[Mysql] mysql database Common commands, mysql database Common commands

[Mysql] mysql database Common commands, mysql database Common commands
1. Database authorization

Go to the mysql database: mysql> use mysql; set a new password for the root user: mysql> update user set password = password ("new password") where user = "root "; refresh the database mysql> flush privileges; nagios monitors mysql> grant process, replication client on *. * TO 'nagiosshow '@ '10. 172.172.12 'identified BY password' * 79b36e3d5f450af5b15934d61d71c031b00002834 '; view User information use mysql; select user, Host, PASSWORD from User; view user Permissions show grants for root @ 'localhost '; delete user form user where Host = 'xxxx' and User = 'xxxx ';

2. Create a database

View existing users: mysql> use mysql; mysql> select Host, User, Password from user; create database marketing_base default character set utf8 COLLATE utf8_general_ci; create a user and grant all privileges on marketing_base. * to dysql @ '%' identified by '1qaz2wsx'; mysql-uroot-p123456 -- default-character-set = gb2312 test <F:/pushingdb. SQL mysql-uroot-p1qaz2wsx -- default-character-set = utf8 ryp_production <cnapsbank. SQL

3. Export the database

Exclude data from some tables when exporting data

You can run two commands. First, export the table structure: mysqldump-uxxx-p-d db_name> db_name. SQL and then export the data you want: mysqldump-uxxx-p dbname -- ignore-table = test. t -- ignore-table = test. t1...> test. SQL; only export database data: mysqldump-uxxx-p-t db_name> db_data. SQL Note: -- ignore-table = xx "=" there cannot be spaces around. The exported database contains the pos and file values mysqldump-uroot -- quick -- flush-logs -- master-data = 1-p ryp1_production> ryp1_production20120140924. SQL.

4. Export the table structure

The specific usage of the command line is as follows: mysqldump-u uses the slave name-p password-d database name Table Name> Script Name; export the entire database structure and data mysqldump-h localhost-uroot-p123456 database> dump. SQL export a single data table structure and data mysqldump-h localhost-uroot-p123456 database table> dump. SQL exports the entire database structure (excluding data)/usr/local/mysql/bin/mysqldump-uroot-d entrym> dump. SQL export a single data table structure (excluding data) mysqldump-h localhost-uroot-p123456-d database table> dump. SQL field separator mysqldump-uroot-p hsb MERCHANT_INFO -- tab = "/tmp/" -- fields-terminated-by = "#; @ "mysql> select * into outfile '/tmp/CASH_USE_INFO. SQL' FIELDS TERMINATED BY '#; @' from CASH_USE_INFO; mysql> select * into outfile '/tmp/MERCHANT_ORDER. SQL' FIELDS TERMINATED BY '#; @' from MERCHANT_ORDER where MERCHANT_ID in (select ID from MERCHANT_INFO ); export the Stored Procedure mysqldump-u database username-p-n-t-d-R Database Name> file name

5. Master-slave synchronization settings

Master database grant replication slave on *. * TO 'backup '@' 192. 168.252.% 'identified BY '20170101'; mysql> show master status \ G slave database mysql> change master to master_host = '2017. 168.252.150 ', master_user = 'backup', master_password = '000000', master_log_file = 'mysql-bin.000003 ', master_log_pos = 123456; mysql> start slave; mysql> show slave status \ G

6. view the database size

Enter the information_schema database (where information about other databases is stored) use information_schema; query the size of all data: select concat (round (sum (data_length/1024/1024), 2), 'mb ') as data from tables; view the size of the specified Database home select concat (round (sum (data_length/1024/1024), 2), 'mb ') as data from tables where table_schema = 'home'; view the size of the members table in the specified Database home select concat (round (sum (data_length/1024/1024), 2), 'mb ') as data from tables where table_schema = 'Home' and table_name = 'members ';

7. query new table Indexes

Check whether the table has an index (either) show index from table_name from db_nameshow index from db_name.table_name view the table structure desc table_name creates an index for the table field (field_name) create index field_name_index on table_name (field_name );

8. Execute SQL statements externally

mysql -uroot -p -e “seclect * from ” > seclect.sql

9. KILL Mysql Processlist ID

#!/bin/bashDATAFILE=`date +%Y%m%d%s`/usr/bin/mysql -uroot -p123456 -e "select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/$DATAFILE.txt';"KILLCOM=`/usr/bin/mysql -uroot -p123456 -e "source /tmp/$DATAFILE.txt"` git fetch 1038  git merge origin/develop

10. Lock table Processing

use information_schemaselect * from INNODB_LOCK_WAITSselect r.trx_id as waiting_trx_id,r.trx_mysql_thread_id as waiting_thread,timestampdiff(second,r.trx_wait_started,current_timestamp) as wait_time,r.trx_query as waiting_query,l.lock_table as waiting_table_lock,b.trx_id as blocking_trx_id,b.trx_mysql_thread_id as blocking_thread,substring(p.host,1,instr(p.host,':')-1) as blocking_host,substring(p.host,instr(p.host,':')+1) as blocking_port,if(p.command='sleep',p.time,0) as idle_in_trx,b.trx_query as blocking_queryfrom information_schema.INNODB_LOCK_WAITS as w inner join information_schema.innodb_trx as b on b.trx_id=w.blocking_trx_id inner join information_schema.innodb_trx as r on r.trx_id=w.requesting_trx_idinner join information_schema.INNODB_LOCKS as l on w.requested_lock_id =l.lock_id left join information_schema.PROCESSLIST as p on p.id=b.trx_mysql_thread_idorder by wait_time desc\G;

11. Analyze tables with frequent operations

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000009 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}' | column -t | sort -k3nr >>~/$$.sql  & 2>/dev/null 

12. Database Analysis Tools

yum install percona-toolkit-2.2.8-1.noarchpt-query-digest mysql-slow-queries_20150313.log

13. Skipping a synchronization error

slave stop;set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;slave start;

14. Enable slow query log

Enable the configuration file (restart required) vi/etc/my. cnflong_query_time = 2log-slow-queries =/var/log/slow. set long_query_time = 2 set global slow_query_log = 1; set global slow_query_file = "/var/log/slow. log ";

15. Stored Procedure execution permission (to be verified)

Definer ('lpdba' @ '%') determines that the definer is lpdbagrant all on mysql. * to lpdba @ '%' identified by 'l1ghtp @ l3 '; grant execute on test. * to lpdba @ '%' identified by 'l1ghtp @ l3 ';

16. Enable event

SHOW VARIABLES LIKE 'event_scheduler';SET GLOBAL event_scheduler = ON;

17. Modify database Triggers

View the TRIGGER creation statement of the database (vas_manage) use vas_manage; show create trigger before_update_game_product; Delete the TRIGGER drop TRIGGER before_update_game_product; create trigger before_update_game_product before update on game_productFOR each rowbegin if locate ("& nbsp;", new. game_type)> 0 THEN set NEW. game_type = REPLACE (NEW. game_type, "& nbsp;", ""); END if; END add execution trigger permission http://www.jb51.net/article/54635.htm

Appendix