MYSQL usage tips (10) ---- Common commands to view the engine showenginesG; view the variable showvariableslike % binlog_format %; view the status showstatuslike % conn %; mysql Log-free execution command mysql-uroot-eshowslavestatusG; mysql specified configuration file login. binmysql -- defau
MYSQL usage tips (10) ---- Common commands to view the engine show engines \ G; view the variable show variables like '% binlog_format %'; view the status show status like '% conn % '; run mysql-uroot-e "show slave status \ G;" mysql specified configuration file logon. /bin/mysql -- defau
MYSQL usage tips (10) ---- Common commands
View Engine
Show engines \ G;
View Variables
Show variables like '% binlog_format % ';
View status
Show status like '% conn % ';
Mysql logon-free Command Execution
Mysql-uroot-e "show slave status \ G ;"
Mysql specified configuration file Login
./Bin/mysql -- defaults-file =/root/mysql/my. cnf-uroot
Lock table
Flush tables with read lock;
Unlock
Unlock tables;
Mysql debugging syntax
After an error is reported, run show warnings;
Detailed information is displayed.
Open ROOT remote access. Generally, do not do this.
Grant all privileges on *. * TO 'root' @ '%' with grant option;
Garbled
Log on to MySQL through the command line to view the Encoding
Mysql> show variables like '% character %'
Create a database
Create database my_test;
Authorization
Grant all privileges on my_test. * to my_test @ '%' identified by 'my _ test_123 ';
Change root Password
Update mysql. user set password = password ('open') where user = 'root ';
Flush privileges;
Back up data (you can manually specify the socket parameter -- socket =/application/search/mysql/tmp/mysql. sock)
./Mysqldump-uroot my_test-v>/data0/search/my_test. SQL
./Mysqldump-uroot-popen opendata-v>/data0/search/my_test. SQL
Backup stored procedures and functions
./Mysqldump-uroot-popen-n-t-d-R opendata1>/data0/search/opendata‑produce. SQL
Back up all database snapshots
Mysqldump
-Uroot-p-h127.0.0.1-P3306 -- all-databases -- triggers -- routines -- events> all. SQL
Import
1) log on first. First, log on to mysql. You can see the import process. If the terminal network is disconnected, the import ends.
Source/data0/search/my_test. SQL
2) You do not need to log on to the console to execute the import command, but cannot see the process.
./Mysql-uroot my_test </data0/search/my_test. SQL &
Number of rows in the statistical table
Use INFORMATION_SCHEMA;
Select TABLE_ROWS, TABLE_NAME, TABLE_SCHEMA from tables where TABLE_SCHEMA = 'my _ test ';
Select sum (TABLE_ROWS) from tables where TABLE_SCHEMA = 'my _ test ';
View server threads
Show processlist;
View the structure of the created table
Show create table name;