MySQL Management _ Database Common commands

1. Database Empowerment

  Enter MySQL database: mysql  > use MySQL; To 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 monitor MySQL  > GRANT process , REPLICATION CLIENT on *. * to ' nagiosshow ' @ ' 10.172.172.12 ' identified by PASSWORD ' * for  [Email protected] ' localhost ' Delete User Delete users form username user where Host  = ' xxxx ' and user= ' xxxx '; 
 

viewing users and Permissions

2. Create a new database

View existing users: MySQL> Use mysql;mysql>DEFAULT  CHARACTER SET UTF8 COLLATE utf8_ General_ci; Create a new user and empower grant all privileges on marketing_base. * to [e-mail protected] '%' identified by ' 1qaz2wsx '  ; MySQL-uroot-p123456-default-character-set =gb2312 test<f:/-uroot-p1qaz2wsx--default-character-set=utf8 ryp_production  <  Cnapsbank.sql

New Database

3. Export the database
Exclude data from some tables when exporting data

-uxxx-p-D db_name>-uxxx-p dbname--ignore-table=test.t--ignore-table=test.t1...>>-uxxx -p-t db_name > db_data.sql Note:--ignore-table=xx "=-uroot--quick--flush-logs--master-data=1-p Ryp1_production > Ryp1_production20140924.sql

mysqldump Exporting a database

4. Export Table structure

the following command-line usage is as follows: Mysqldump-u user name-p password-d database name Table name >the name of the script; 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 Export Entire database structure (no data included)/usr/local/mysql/bin/mysqldump-uroot-d entrym>Dump.sql export a single data table structure (no data included) Mysqldump-H localhost-uroot-p123456-d database table >dump.sql field delimiter 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 stored Procedure mysqldump-U database user name-p-n-t-d-r database name > file name

mysqldump export Table Structure

5. Master-Slave synchronization settings

* * to ' backup ' @ ' 192.168.252.% ' identified by ' 123456'; MySQL> show Master status\g from database MySQL> Change Master to  master_host= ' 192.168.252.150 ', master_user= ' backup ', master_password= ' 123456 ', master_log_file = ' mysql-bin.000003 ', master_log_pos=107; MySQL> start slave;mysql> SHOW slave status\g

Master-Slave synchronization settings

6. View the database size

Enter the INFORMATION_SCHEMA database (information that holds other databases) 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 home in the specified database 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 home of the specified database select concat (Round (SUM (data_ Length/1024/1024), 2), ' MB ') as data from the tables where table_schema= ' home ' and table_name= ' members ';

View Database Size

7. Querying the new table index

Querying new Table Indexes

8. External Execute SQL statement

Mysql-uroot-p-E "Seclect * from" > Seclect.sql

external Execution SQL

9.KILL Mysql processlist ID

# !/bin/bash datafile= ' Date +%y%m%d%s '"Select concat (' KILL ', id, '; ') From information_schema.processlist where user= ' root ' into outfile '/tmp/$DATAFILE. txt '; " killcom " source/tmp/$DATAFILE. txt "  1038  git merge Origin/develop

KILL Mysql processlist ID

10 Lock Table Handling

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, ': ')-1as 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;

Lock Table Handling

11. Analyze frequently-operated tables

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  

analyze frequently-operated tables

12. Database analysis Tools

Yum install percona-toolkit-2.2.8-1. Noarchpt-query-digest Mysql-slow-queries_20150313.log

Database analysis Tools

13. Skipping a sync error

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

skipping a synchronization error

14. Turn on the slow query log

/etc/= 2log-slow-queries=/var/log/slow.log Online on (no restart required) set Long_query_time=2= 1  "/var/log/slow.log";

turn on slow query log

15. Stored Procedure Execution permissions

Definer (' lpdba ' @ '% ')  determines that the definition is lpdbagrant all on MySQL. * to [email protected] '% ' identified by ' [e-mail protected]L3 '; Grant execute on test. * to [e-mail protected] '% ' identified by ' [email protected] ';

15. Stored Procedure Execution Permissions

16. Turn on events

= on;

16. Turn on Events

17. Modifying database triggers

view trigger creation statement for database (vas_manage) Use vas_manage;show create TRIGGER before_update_game_product; Delete Trigger drop TRIGGER before_ update_game_product; creating a trigger Create TRIGGER before_update_game_product before update on game_product forEach ROWBEGIN    IFLOCATE (" ", New.game_type) >0Then set New.game_type=replace (New.game_type," "," "); END if;END

Modify a Database trigger

MySQL Management _ Database Common commands