MYSQL user experience (10)-Common commands

Source: Internet
Author: User
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.