Summary of mysql commands

This article mainly explains the mysql commands in centoslinux (redhatlinux). If your mysql is installed in source code, I add the "mysql installation directory bin" to the path environment variable, mysql startup script has been copied to etcinit. d directory with execution permission 1. start/stop a database
Start

Service mysqld start
/Etc/init. d/mysqld start


Close
Service mysqld stop
/Etc/init. d/mysqld start
Mysqladmin-u root-p shutdown

II. change the user password and grant permissions to the mysql database
2.1 modify the mysql User password

Method 1
Mysqladmin-u username-p' password: 'password' new password'

Method 2

By modifying (inserting) the user table record in mysql
#/Var/mysql5/bin/mysql-h localhost-uroot-p
Enter password: Do not Enter anything (Enter the new root password for the database)
Mysql> use mysql
Mysql> update user set password = password ('New password') where USER = 'username' and HOST = 'hostname ';
Modify the where condition of the SQL statement as needed. the host can be % (any host), IP address, and localhost.

# Change the password to take effect. you must refresh the permission.
Mysql> flush privileges;
Or
Mysqladmin-h localhost-uroot-ppassword reload
Refresh the system permission table, which must not be less effective.

Method 3
Grant authorization command
#/Var/mysql5/bin/mysql-h localhost-uroot-p
Enter password: Do not Enter anything (Enter the new root password for the database)
Mysql> grant permission 1, permission 2 ,... Permission n on database. table to user @ host name identified by 'user password ';
For example:
Mysql> grant all on *. * to root @ '%' identified by '123'
Mysql> grant all on *. * to root @ localhost identified by '20140901'

--------------------------------------
Permission 1, permission 2 ,... Permission n:
Select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file, and other 14 permissions.
When permission 1, permission 2 ,... Permission n is replaced by all privileges or all, indicating that all permissions are granted to the user.
When the database name. table name is replaced by *. *, it grants the user the permission to operate all the tables in the database on the server.
The user address can be localhost, IP address, machine name, or domain name. You can also use '%' to connect from any address.
The 'connection password' cannot be blank; otherwise, creation fails.
-------------------------------------
Mysql> flush privileges;
Or
Mysqladmin-h localhost-uroot-ppassword reload
Refresh the system permission table, which must not be less effective.

III. mysql common database, table, field, and index management operations

3.1 create database commands and create databases

# When database databasename does not exist, it is added and the utf8 character set and utf8_general_ci verification character set are specified.
Create database if not exists databasename default charset utf8 COLLATE utf8_general_ci;

# Directly create database databasename. The character set is specified by mysql default configuration (depending on my. cnf configuration)
Create database databasename;

# When the database databasename does not exist, it is added and the character set utf8 is specified.
Create database if not exists databasename default charset utf8

3.2 create a table
# Delete and recreate a table if it exists (no prompt)
Drop table if exists 'table name ';
Create table 'table name '(
'Field value 1' int (11) not null AUTO_INCREMENT,
'Field value 2' varchar (30) default null,
......
Primary key ('field value 1 ')
) ENGINE = storage ENGINE (such as MyISAM, innodb) AUTO_INCREMENT = 1 default charset = character set (such as utf8 );

# Directly create a table (if the table exists, the system will prompt that the table already exists)
Create table 'table name '(
'Field value 1' int (11) not null AUTO_INCREMENT,
'Field value 2' varchar (30) default null,
......
Primary key ('field value 1 ')
) ENGINE = storage ENGINE (such as MyISAM, innodb) AUTO_INCREMENT = 1 default charset = character set (such as utf8 );

For example:
Drop table if exists 'yuance ';
Create table 'yuanceshi '(
'Goods _ no' varchar (30) default null,
'Pid _ color' varchar (30) default null,
'Products _ no' varchar (30) default null,
'Create _ time' datetime default null,
Primary key ('goods _ id ')
) ENGINE = MyISAM AUTO_INCREMENT = 724 default charset = utf8;

Create table 'yuanceshi '(
'Goods _ no' varchar (30) default null,
'Pid _ color' varchar (30) default null,
'Products _ no' varchar (30) default null,
'Create _ time' datetime default null,
Primary key ('goods _ id ')
ENGINE = INNODB AUTO_INCREMENT = 724 default charset = utf8;

3.3 Field Management
# Add a field
Alter table name add field name field type
For example
# Add an integer field
Alter table table_name add filed_name int (11) DEFAULT '0 ';
# Add a date row field
Alter table yuanshi add up_time datetime default null;
# Add a micro-integer field
Alter table yuanshi add is_del tinyint (1) not null default '0 ';
# Add a String Field
Alter table yuanshi add unit char (10) default null;
Alter table yuanshi add description varchar (255) default null;

# Add a field after the end_time field
Alter table table_name add filed_name int (11) DEFAULT '0' AFTER 'end _ time ';

# Modify the field point
Alter table 'linuxshizhan 'modify COLUMN 'point' int (11) not null default 0 COMMENT 'integral ';
# Modify the point field next to the weight field
Alter table 'linuxshizhan 'modify COLUMN 'point' int (11) not null default 0 comment' credits 'after 'weight ';

# Delete a field tag_ids
Alter table 'linuxshizhan 'drop COLUMN 'tag _ id ';

3.4 create index adds an index to a field
Syntax:
Create [unique | fulltext | spatial] index index_name
[Using index_type]
On tbl_name (index_col_name ,...)
Where:
Index_name: name of the index, which must be unique in a table.
USING index_type: some storage engines allow you to specify the index type when creating an index. Index_type is the name of the index type supported by the storage engine. The index types supported by MySQL include BTREE and HASH. If the USING clause is not specified, MySQL automatically creates a BTREE index.
Index_col_name: col_name indicates the name of the column that creates the index. Length indicates that the index is created using the first length of the column. Using a part of a column to create an index can greatly reduce the index file and save disk space. In some cases, you can only index the column prefix. For example, the length of an index column has a maximum limit. Therefore, if the length of an index column exceeds this limit, you may need to use the prefix for indexing. BLOB or TEXT columns must be indexed with a prefix. The prefix is up to 255 bytes, but for MyISAM and InnoDB tables, the prefix is up to 1000 bytes. You can also specify whether the index is sorted in ascending or descending order (DESC). The default value is ASC. If a column in a SELECT statement is sorted in descending order, defining a descending index on the column can speed up processing.
UNIQUE | FULLTEXT | SPATIAL: UNIQUE indicates that a UNIQUE index is created. FULLTEXT indicates that a full-text index is created. SPATIAL indicates that a SPATIAL index can be used to index columns of the geometric data type.

Important:
The create index statement cannot CREATE a primary key.
The definition of an index contains multiple columns separated by commas, but they must belong to the same table. Such an index is called a composite index.

For example:
Create index idx_firstLetter on tb_drug_base (firstLetter );

3.5 add an index through alter table

1. primary key (primary key index)
Alter table 'Table _ name' add primary key ('column ')

2. UNIQUE (UNIQUE index)
Alter table 'Table _ name' add unique ('column ')

3. INDEX (common INDEX)

Alter table 'Table _ name' add index index_name ('column ')

4. FULLTEXT (full-text index)
Alter table 'Table _ name' add fulltext ('column ')

5. multi-column index
Alter table 'Table _ name' add index index_name ('column1 ', 'column2', 'column3 ')

3.6 View indexes
Show index from tbl_name [FROM db_name]

IV. Set Database encoding (character set)

. Set the entire database encoding

A. When mysql is started, add the mysqld_safe command line
-- Default-character-set = gbk
Gbk is character set encoding

B. modify my. cnf and add it to [mysqld ].
Default-character-set = gbk
Gbk is character set encoding

. Change the encoding format of a database: enter the command at the mysql prompt
# Mysql installation directory/bin/mysql-hlocalhost-uroot-p
Mysql> alter database name default character set gbk;
Gbk is character set encoding
Display character set encoding variables
Mysql> show variables like 'collation _ % ';
+ ---------------------- + ----------------- +
| Variable_name | Value |
+ ---------------------- + ----------------- +
| Collation_connection | utf8_general_ci |
| Collation_database | utf8_general_ci |
| Collation_server | utf8_general_ci |
......................................................
+ ---------------------- + ----------------- +
Mysql> show variables like 'character _ set %'
+ -------------------------- + -------- +
| Variable_name | Value |
+ -------------------------- + -------- +
| Character_set_client | latin1 |
| Character_set_connection | latin1 |
| Character_set_database | latin1 |
| Character_set_filesystem | binary |
| Character_set_results | latin1 |
| Character_set_server | latin1 |
| Character_set_system | utf8 |
......................................................
+ -------------------------- + -------- +
Set character set variables
Mysql> SET character_set_client = gbk;
Mysql> SET character_set_connection = gbk;
Mysql> SET character_set_results = gbk;

V. common mysql management commands

Mysql> shell
Show databases; show all databases
Show processlist; view the mysql process List
Show full processlist; view the mysql process list, including detailed SQL
Show tables; show all tables
Show variables; display variables
Use databasename; switch database
Desc tablename; view the table structure
Show table status; View table status
Show status; View system status
Show slave status; view slave database status
Flush tables with read lock; LOCK the database. Generally, before the backup money is used or the master/slave snapshot is created
Unlock tables; UNLOCK. Generally, when the backup is completed or the master/slave snapshot is created

Mysqladmin-h localhost-uroot-ppassword processlist view the mysql process List


VI. mysql log management
Mysql> flush logs;
# By File: remove logs before the mysql-bin.000354, excluding the mysql-bin.000354
MYSQL> purge binary logs to 'MySQL-bin.000354 ';

Query OK, 0 rows affected (0.16 sec)

# Delete logs by time before 00:00:00

MYSQL> purge binary logs before '2017-08-10 00:00:00 ';

# By Time: handle logs generated three days ago

MYSQL> purge master logs before date_sub (now (), interval 3 day );

Automatic log cleanup:

# Modify the bin-log expiration time in the my. cnf file configuration. for example, if the log is retained for 7 days

[Mysqld]

Expire-logs-days = 7

Of course, after the flush logs action is performed, directly go to the mysql data folder and directly use rm-f mysql-bin. related files (the latest log file cannot be deleted !!!)
Assuming the latest log file is mysql-bin.000354, then you can directly go to the mysql data folder
Rm-f mysql-bin.000353
Rm-f mysql-bin.000352
If you are not sure, you can use ls-lht to confirm the latest log file in the mysql data folder.
Of course, I suggest you use purge binary logs to delete the comparison insurance.


VII. common mysql database backup
7. 1. free tools

A. directly copy data files. you need to stop the database and do not need to generate the system.
Directly copying data files is the most direct, fast, and convenient, but the disadvantage is that incremental backup is basically not supported. To ensure data consistency, run the following SQL statement before backing up the file:
Mysql> flush tables with read lock; (or stop the database, not applicable to the production system .)
That is, the data in the memory is refreshed to the disk, and the data table is locked to ensure that no new data is written during the copy process. In this way, the backup data recovery is also very simple, just copy it back to the original database directory.
B. for mysqlisam storage, hotcopy and mysqldump can be used.
C. for innodb storage, use mysqldump
D. use mysqldump of innodb and mysqlisam simultaneously
E. master-slave replication (when the master database data is accidentally or maliciously deleted, the slave database used as the backup will not be available .)

7.2. Commercial software
IBBackup

7.3 mysql database physical backup: directly copy mysql database files
Stop the mysql database first
Service mysqld stop

Run system commands
# Cp-r mysql database data directory backup file storage path
Or
# Tar-cvzf example file name .tar.gz data directory of the mysql database

Restore the physical backup of the mysql database:
Directly overwrite the file to the data directory of the corresponding database.

7.4 mysql hotcopy hot backup, only for mysiam storage engine
MySQLHotCopy is a perl script that locks the database table during execution, and then uses the system command cp or scp for a backup. When the backup is complete, release the table lock and refresh the log. it is a very quick method, but data backup can only be performed on one machine, only suitable for mysqlisam storage.
Command
# Mysql installation directory/bin/mysqlhotcopy -- user = user -- password = password-q "database name" backup file storage directory

Restore:
Directly overwrite the file to the data directory of the corresponding database.


7.5 mysqldump backup
It is suitable for storage of mysqlisam and innodb, and is often used for data backup and migration.
Mysqldump-h host-u user-p pass-B database name> database backup name. SQL
Back up all the tables in the database "database name" to the "database backup name. SQL" file,
"Database Backup name. SQL" is a text file with any file name.
The SQL backed up by this command carries the create dabatase information, that is, the backed up SQL statement will delete and recreate the existing database during restoration.

For example
Mysqldump-uroot-pdcbicc106-B mysql> mm. SQL

# Mysqldump without database creation information
Mysqldump-h host-u user-p pass-B database name> database backup name. SQL
For example
Mysqldump-uroot-pdcbicc106-B-n mysql> mm2. SQL

# Mysqldump-h host-u user-p pass -- opt database name table 1 TABLE 2 table 3 ...... Table n> database backup name. SQL
Replace table 1, table 2, and Table 3 in the database "database name ...... Table n is backed up to the "database backup name. SQL" file,
"Database Backup name. SQL" is a text file with any file name.

# Mysqldump-h host-u user-p pass -- databases Database 1 Database 1> database backup name. SQL
Back up "database 1" and "Database 2" to the "database backup name. SQL" file,
"Database Backup name. SQL" is a text file with any file name.

# Mysqldump-hhostname-uusername-ppassword-no-data -- databases databasename1 databasename2 databasename3> multibackupfile. SQL
Back up database structures only

# Mysqldump-h host-u user-p pass -- opt -- all-databases> all-databases. SQL
Back up all the databases to the all-databases. SQL file. the all-databases. SQL is a text file with any file name .)

Restore a MySQL database

# Mysql-hhostname-uusername-ppassword databasename <backupfile. SQL
# Mysql -- default-character-set = utf8-u xxx [database] <xxx. SQL

Restore a compressed MySQL database
# Gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename
# Gunzip <backupfile. SQL .gz | mysql -- default-character-set = utf8-u xxx [database]

Transfer Database to new server
# Mysqldump-uusername-ppassword databasename | mysql? Host = *. *-C databasename


Backup is stored in innodb
Dump reference options:
-- Opt -- default-character-set = utf8 \
-- Triggers-R -- hex-blob -- all-databases \
-- Flush-logs \
-- Single-transaction \
-- Delete-master-logs \
-X


7.6 export the table structure
Mysqldump-h192.168.1.30-uroot-123456 -- default-character-set = utf8-B backdb-d>/bak/biaojiegou. SQL

The -- set-GTID-purged = OFF parameter needs to be added for the database whose global transaction gtid is used for mysql5.6. Otherwise, an error is reported.
Mysqldump-h192.168.1.30-uroot-123456 -- default-character-set = utf8 -- set-gtid-purged = OFF-B backdb-d>/bak/biaojiegou. SQL

7.7 export only the trigger and backup only the trigger
Mysqldump-h127.0.0.1-uroot-p123456 -- skip-opt -- trigger-d-n-t-B backdb>/bak/backdb_trigger

7.8 export functions funtcion and event
Mysqldump-h192.168.1.30-uroot-p123456 -- default-character-set = utf8-n-d-t-R-E dbname>/bak/funcation. SQL
The -- set-GTID-purged = OFF parameter needs to be added for the database whose global transaction gtid is used for mysql5.6. Otherwise, an error is reported.
Mysqldump-h192.168.1.30-uroot-p123456 -- default-character-set = utf8 -- set-gtid-purged = OFF-n-d-t-R-E dbname>/bak/funcation. SQL

7.9 only backup database data, without any table structures and functions, triggers
Mysqldump-uroot-pdcbicc106-n-t-B mysql> mydata. SQL

8.0 export data using the table sharding method. this method is only suitable for backup and restoration and can only be performed on the server where mysql is located, the backup directory {BACKUPDIR} must have the permission of the mysql running user (usually the mysql User). remember
If no, please chown mysql: mysql {BACKUPDIR}
Suitable for table sharding export: NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

Mysqldump-uroot-p $ {MYSQLPASSWORD}-T $ {BACKUPDIR }$ {DATABASENAME}
Mydump-h localhost-uroot-ppassword-T backup directory database name table name

Table sharding data restoration:
Mysqlimport-h local-uroot-p '000000' backdb' find/data/backup/database/backdb/123456-02-27/-name "*. txt "'

VIII. mysql slow query log sharing tool

Mysqldumpslow: perl tool, which is officially provided by mysql
Mysqlsla: The perl tool has powerful functions, complete data reports, and strong customization capabilities.
Mysql-explain-slow-log: perl none
Mysql-log-filter: keep the output concise without losing the functionality of python or php.
Myprofi: php tool, very streamlined


Mysql slow query log analysis tool mysqlsla is recommended here

Example
Display the 10 SQL statements with the longest execution time in mysqlslow. log and write them to SQL _10.log.
Mysqlsla-lt slow-sf "+ select"-top 10 mysqlslow. log> SQL _10.log

In the slow log mysqlslow. log, all the select and update slow SQL statements in the database for mydb are displayed, and the maximum number of queries is 100 in SQL _su100. SQL.
Mysqlsla-lt slow-sf "+ select, update"-top 100-sort c_sum-db mydb mysqlslow. log> SQL _su100.log

Mysqlsla-lt slow-sf "+ select"-top 100-sort c_sum mysqld_slow_query.log> SQL _su100.log

9: binlog log analysis: parses write SQL statements using mysqlbinlog. it is often used in mysql to restore data through binlog.
Example:
Enter the mysql data folder, such as/var/lib/mysql/
Cd/var/lib/mysql/
Mysqlbinlog -- start-datetime = "00:00:00" -- stop-datetime = "23:59:59" mysql-bin.000098