Mysql practice-level commands for beginners

Mysql practice level: mysql beginners common command Creation Time: 4.04.08.24 modification time: 2014.09.26 from the perspective of an O & M engineer and DBA novice, learn and practice to master mysql related operations. 1. Login

Mysql practice level: mysql beginners common command Creation Time: 4.04.08.24 modification time: 2014.09.26 from the perspective of an O & M engineer and DBA novice, learn and practice to master mysql related operations. 1. Login

Mysql practice-level commands for beginners

Creation Time:

Modification time:

From the perspective of a novice O & M engineer and DBA, learn and practice mysql-related operations.

1. log on to the MySQL server

When facing a running mysql service, the first thing we need to do is log on to the mysql service.

The MySQL client can connect to the mysqld server in two different ways:

Non-Standard Port Logon

Non-3306 service port logon, for example:

Mysql-u qunyingliu-p 51cto20140824-P 3307 mysql-h 10.1.8.24-u qunyingliu-p 51cto20140824-P 3307

B. Connect to the mysql service through socket

Mysql-S mysql. sock file address

For example,

Mysql-S/tmp/mysql. sock

Of course, as mentioned above, the following two login methods are connected to the mysql service by default through unix:

Mysqlmysql-P 3307-u qunyingliu-p51cto201401_mysql-h localhost-P 3307-u qunyingliu-p51cto20140824

If you connect to mysql through socket, when the mysql. sock file is not the default name or storage path, we will receive an error message similar to the following:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql. sock'

2. Common Mysql interactive commands

Cancel authorization:
Revoke all privileges (permission) on * (database). * (table) from account @ host;
For example,

Mysql> revove all privileges on *. * from qunyingliu @ "% ";

Delete A User:
Delete from user where user = "Account" and host = "% ";
For example,

Delete from user where user = "qunyingliu" and host = "% ";

Database/data table/data column permissions:

Alter: Modify existing data tables (such as adding/deleting columns) and indexes.

Create: Create a new database or data table.

Delete: Delete table records.

Drop: delete a data table or database.

INDEX: Create or delete an INDEX.

Insert: Add Table records.

Select: displays/searches for table records.

Update: Modify existing records in the table.

Global Management of MySQL user permissions:

File: read and write files on the MySQL server.

PROCESS: displays or kills service threads of other users.

RELOAD: RELOAD Access Control tables and refresh logs.

SHUTDOWN: Shut down the MySQL service.

Special permissions:

ALL: allow anything (same as root ).

USAGE: Only logon is allowed. Other operations are not allowed.

View MySQL user permissions

View Current user (own) permission: show grants; view other MySQL user permissions: show grants for qunyingliu@10.2.122.1;

Mysql service operations

View mysql version and current time:
Select version (), current_date;
Change Password:
Update user set password = password ("liuqunying") where user = 'qunyingliu'; flush privileges (refresh permission)

3. Mysql data backup and recovery

Back up the database:

Mysqldump-h host-u root-p database name -- default-character-set = utf8 [| gbk | latin1]> dbname_backup. SQL

Backup data table:

Mysqldump-h host-u root-p database name Table Name> dbname_tablename_backup. SQL

Only SQL commands for data insertion can be exported:

Mysqldump-h host-u root-p-t database name [Table name]> insert_data. SQL

Export only the SQL command for creating a table:

Mysqldump-h host-u root-p-d database name [Table name]> create_table. SQL

Export only the data in the table:

Mysqldump-h host-u root-p-T export data directory database table name

Recover Database: there are multiple methods to restore the database. We recommend that you run the source command to view the data import progress.
Mysql + source command:

Qunyingliu_host # mysql-h host-u root-p mysql> use dbname; source dbname_backup. SQL;

Mysqldump command:

Qunyingliu_host # mysqldump-u username-p dbname <dbname_backup. SQL

Mysql command:

Login # mysql-u username-p-D dbname <mysql usage in the login environment qunyingliu_host # mysql-h host-uqunyingliu-login-e "SQL statement" qunyingliu_host # mysqladmin-h myhost -u root-p "SQL statement" 5. FAQs during mysql usage

Reset the logon password of the root account

Qunyingliu_host #/etc/init. d/mysql stop qunyingliu_host # mysqld_safe -- user = mysql -- skip-grant-tables -- skip-networking & qunyingliu_host # mysql-u root mysql> update user set password = password ('qunyingliu @ 51cto ') where user = 'root'; mysql> flush privileges; mysql> quit qunyingliu_host #/etc/init. d/mysqld restart qunyingliu_host # mysql-uroot-p

To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.
To delete a table, use drop
Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use delete.
If you are sorting out the fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data/

Practical application:
Delete from table; // delete all data
Truncate table; // The auto_increatement modulation starts from 0, actually starting from 1

##########################

Reference:

This article is from the "O & M personnel: From cainiao to laruence" blog. Be sure to keep this source