一、前言
前面几章介绍了 MySQL 的安装和简单的配置，只会这些还不够，作为 Linux 系统管理员，我们还需要掌握一些基本的操作，以满足日常管理工作所需。MySQL环境中的命令需要带一个分号作为命令结束符。
MySQL 之父 Widenius 先生离开了 Sun 之后，觉得依靠 Sun/Oracle 来发展 MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做 MariaDB。MariaDB 跟 MySQL 在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。目前 MariaDB 是发展最快的 MySQL 分支版本，新版本发布速度已经超过了 Oracle 官方的 MySQL 版本。
二、常用操作
2.1 增加新用户和授权
连接 MySQL 数据库的命令为：
[root@ryan linux]# mysql -h192.168.1.121 -uroot -p123456 -D mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
-h 指定远程MySQL数据库服务器地址，-u 指定登录用户名，-p指定密码，-D指定要直接连接到哪个数据库，默认连接端口为 3306，也可以使用 -P 选项来指定其他端口。
也可以使用 socket 登录，如下：
[root@ryan linux]# mysql -uroot -S /tmp/mysql.sock -p123456
增加一个用户 test1 密码为 123456，让他可以从任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。
首先用以root用户连入MySQL，然后键入以下命令：
[root@ryan linux]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> grant select,insert,update,delete on *.* to 'test1'@'%' identified by '';
Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) mysql> exit
Bye
[root@ryan linux]# mysql -utest1 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
让 root 用户可以从任何主机上登录，并对所有数据库有所有权限，如下：
[root@ryan linux]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> grant all on *.* to 'root'@'%' identified by '';
Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
让用户 rain 从指定客户端地址 192.168.1.101 访问 MySQL 数据库，并对所有数据库有所有权限，如下：
mysql> grant all on discuz.* to 'rain'@'192.168.1.101' identified by '';
Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
其中，all 表示所有的权限（包括读、写、查询、删除等操作）；discuz表示数据库，*表示所有的表，如果是*.*则表示所有的数据库和所有的表；identified by 后面跟密码，用单引号括起来；用户和主机的IP之间有一个符号 '@'，命令中的 IP 也可以使用 '%' 代替，表示所有主机。
授权前后对比，客户端 windows 地址为 192.168.1.101，MySQL 服务器端 Linux 地址为 192.168.1.121，如下：
C:\Users\Administrator>mysql -urain -h192.168.1.121 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'rain'@'192.168.1.101' (using password: YES) C:\Users\Administrator>mysql -urain -h192.168.1.121 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
远程连接 MySQL 服务器时，需要确保放行 3306 端口，如下：
[root@ryan linux]# iptables -I INPUT -p tcp --dport -j ACCEPT
[root@ryan linux]# /etc/init.d/iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
也可以直接修改 iptables 配置文件 /etc/sysconfig/iptables，添加3306放行规则，如下：
[root@ryan linux]# vim /etc/sysconfig/iptables
-A INPUT -p tcp -m tcp --dport -j ACCEPT
[root@ryan linux]# service iptables restart
常见错误：
错误1：
# mysql -uroot -h192.168.1.121 -p
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.121' (10060)
出现这个错误的原因可能是：远程3306端口未对外开放。
错误2：
[root@ryan linux]# mysql -uroot -h192.168.1.121 -p123456
ERROR 1130 (HY000): Host 'xxx' is not allowed to connect to this MySQL server
出现这个错误的原因可能是：此账号不允许远程登录 192.168.1.121 MySQL 数据库主机，即不允许远程访问，只允许从本机 localhost 访问。可以进行如下修改来来允许远程访问：
mysql> select host,user from mysql.user\G
*************************** 1. row ***************************
host: localhost
user: root
…… mysql> update mysql.user set host = '%' where user ='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) mysql> select host,user from mysql.user where user='root';
+------+------+
| host | user |
+------+------+
| % | root |
+------+------+
1 row in set (0.00 sec)
如上修改后，再次连接数据库服务器，发现已经可以远程连接了。
错误3：
[root@ryan linux]# mysql -urain -h192.168.1.121 -p123456
ERROR 1045 (28000): Access denied for user 'rain'@'192.168.1.121' (using password: YES)
出现这个错误的原因可能是：密码错误。
2.2 常用基本操作
修改 MySQL 数据库密码
MySQL 中有多种修改密码的方式，如下：
格式：mysqladmin -u用户名 -p旧密码 password 新密码
[root@masternode ~]# mysqladmin -uroot -p'' password ''
或者使用 SET PASSWORD 命令，如下：
格式：mysql> set password for 用户名@localhost = password('新密码');
mysql> set password for root@localhost = password('');
mysql 中 \g 和 \G 的作用
\g 的作用是分号和在 sql 语句中写 ';' 是等效的
\G 的作用是将查到的结构旋转 90 度变成纵向
如下：
mysql> select * from user_test;
+-------+------+--------+
| id | name | phone |
+-------+------+--------+
| 10001 | ryan | 111111 |
| 10002 | tom | 222222 |
| 10003 | Lucy | 333333 |
| 10004 | Lili | 444444 |
+-------+------+--------+
4 rows in set (0.00 sec) mysql> select * from user_test\g
+-------+------+--------+
| id | name | phone |
+-------+------+--------+
| 10001 | ryan | 111111 |
| 10002 | tom | 222222 |
| 10003 | Lucy | 333333 |
| 10004 | Lili | 444444 |
+-------+------+--------+
4 rows in set (0.00 sec) mysql> select * from user_test\G
*************************** 1. row ***************************
id: 10001
name: ryan
phone: 111111
*************************** 2. row ***************************
id: 10002
name: tom
phone: 222222
*************************** 3. row ***************************
id: 10003
name: Lucy
phone: 333333
*************************** 4. row ***************************
id: 10004
name: Lili
phone: 444444
4 rows in set (0.01 sec)
查看有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
MySQL 中使用 Linux 命令
mysql> system pwd
/root/linux
MySQL 查询某个库的表
Database changed
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
查询某个表的全部字段
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
还可以显示的更加详细，而且还可以把建表语句列出来：
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '',
`max_updates` int(11) unsigned NOT NULL DEFAULT '',
`max_connections` int(11) unsigned NOT NULL DEFAULT '',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
可以看到系统user表的主键为 Host 和 User 组合主键。其中 \G 表示规范化显示结果。
查看当前是哪个用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前所使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
创建一个新数据库
mysql> create database db1;
Query OK, 1 row affected (0.01 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| discuz |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
创建一个新表
mysql> use db1;
Database changed
mysql> create table client ('id' int(8), 'name' char(40));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''id' int(8), 'name' char(40))' at line 1
mysql> create table client (`id` int(8), `name` char(40));
Query OK, 0 rows affected (0.34 sec)
使用命令行模式创建表时，字段前后加反引号，而不能加单引号。
查看当前MySQL数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22 |
+-----------+
1 row in set (0.00 sec)
查看MySQL的当前状态
mysql> show status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients | 0 |
| Aborted_connects | 10 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 10082 |
结果太多，此处只列出一部分。
查看MySQL的参数
mysql> show variables;system head
+----------------------------------------------------------+-----------------------------+
| Variable_name | Value |
+----------------------------------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| back_log | 80 |
| basedir | /usr/local/mysql/
参数太多，此处只列出一部分，其中有许多参数可以在 /etc/my.cnf 中定义。
修改 MySQL 的参数
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.01 sec) mysql> set global max_connect_errors=200;
Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 200 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.01 sec)
在 MySQL 命令行中，符号 "%" 类似于 shell 下的 "*"，表示万能匹配。使用命令 "set global" 可以临时修改某些参数，但是重启MySQL服务后这些修改会失效。所以，如果你想让这些修改恒久生效，就要在配置文件 /etc/my.cnf 中定义。
查看当前 MySQL 服务器的队列
查看服务器队列在日常的管理中比较频繁，可以通过它知道当前 MySQL 在干什么，也可以发现是否有锁表。
mysql> show processlist;
+----+------+---------------------+--------------------+---------+-------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+--------------------+---------+-------+----------+------------------+
| 12 | root | 192.168.1.101:56915 | mysql | Sleep | 18357 | | NULL |
| 26 | root | localhost | db1 | Query | 0 | starting | show processlist |
| 27 | root | 192.168.1.101:64738 | NULL | Sleep | 2305 | | NULL |
| 28 | root | 192.168.1.101:64742 | discuz | Sleep | 2316 | | NULL |
| 29 | root | 192.168.1.101:64746 | information_schema | Sleep | 2298 | | NULL |
+----+------+---------------------+--------------------+---------+-------+----------+------------------+
5 rows in set (0.00 sec)
2.3 常用的 SQL 语句
插入一行
mysql> insert into client('id','name') values(1001,'Bob');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''id','name') values(1001,'Bob')' at line 1
mysql> insert into client(`id`,`name`) values(1001,'Bob');
Query OK, 1 row affected (0.00 sec)
可以看到字段名称依然需要使用反引号包裹。
查询语句
mysql> select * from client;
+------+------+
| id | name |
+------+------+
| 1001 | Bob |
| 1002 | Adam |
| 1003 | Deft |
| 1004 | eft |
+------+------+
4 rows in set (0.00 sec) mysql> select name from client;
+------+
| name |
+------+
| Bob |
| Adam |
| Deft |
| eft |
+------+
4 rows in set (0.00 sec)
更新数据
mysql> update client set name='Deft' where id=1004;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除数据
mysql> delete from client where id=1003;
Query OK, 1 row affected (0.01 sec) mysql> select * from client;
+------+------+
| id | name |
+------+------+
| 1001 | Bob |
| 1002 | Adam |
| 1004 | Deft |
+------+------+
3 rows in set (0.00 sec)
清空某个表的数据
mysql> truncate table client;
删除表结构
mysql> drop table client;
删除数据库
mysql> drop database db1;
2.4 MySQL 数据库的备份和恢复
MySQL 数据库备份
[root@ryan linux]# mysqldump -uroot -p123456 db1 > /tmp/mysql_db1_20180805.sql
[root@ryan linux]# ll /tmp/mysql_db1_20180805.sql
-rw-r--r-- root root Aug : /tmp/mysql_db1_20180805.sql
MySQL 数据库恢复
[root@ryan linux]# mysql -uroot -p123456 db1 < /tmp/mysql_db1_20180805.sql
上面的 db1 为需要备份和恢复的数据库名称，/tmp/mysql_db1_20180805.sql备份的 sql 文件或者要恢复 sql 文件，这个文件中既包含建表语句，也包含表中数据，如果只想保存建表语句，则添加 -d 选项。也可以在备份或者恢复的时候指定字符集，使用 --default-character-set=utf8。如果只想备份单个表，可以在数据库名后面添加表名，表示只备份指定表。
常用设置
关闭 MySQL 的 DNS 反查功能
在 my.cnf 中添加 skip-name-resolve，如下：
[root@ryan ~]# vim /etc/my.cnf
skip-name-resolve
当服务器放在局域网内进行测试时，数据库的访问速度还是很快。但当服务器放到外网后，数据库的访问速度就变得非常慢。不管远程连接 MySQL 的方式是经过 hosts 或是 IP 的模式，他都会对 DNS 做反查。mysqld 会尝试去反查 IP -> dns ，由于反查解析过慢，就会无法应付过量的查询。禁用 DNS 反向解析，就能大大加快 MySQL 连接的速度。不过，这样的话就不能在 MySQL 的授权表中使用主机名了而只能用 IP 格式。
查看当前存储引擎
mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.01 sec) ##查看系统支持的引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
skip-innodb 参数表示忽略 innodb 存储引擎（即使其不可用）。
添加该参数之后，/data/mysql/ 目录下将不会出现 ibdata1、ib_logfile0 和 ib_logfile1 文件。ib_logfile 会记录系统的回滚，重做日志。当系统崩溃重启时，用作事务重做；在系统正常时，每次 checkpoint 时间点，会将之前写入事务应用到数据文件中。
配置慢查询日志
/etc/my.cnf 中配置慢查询日志参数，如下：
slow_query_log = 1 #是否开启慢查询日志，1表示开启，0表示关闭。
log_show _queries = /path/to/slow_queries #这个参数（5.6以下版本）用于指定慢查询日志的存放路径。可以不设置该参数，系统则会默认给一个缺省的文件 host_name-slow.log。
slow-query-log-file = /path/to/slow_query #这个参数（5.6及以上版本）用于 MySQL 数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件 host_name-slow.log。
long_query_time = 1 #慢查询阈值，当查询时间多于设定的阈值时，记录日志。
三、安装 phpMyAdmin
phpMyAdmin 是以 web 页面的形式来管理 mysql 数据库，目前好多建站程序包中都带了 phpMyAdmin。下面我们看一下如何手动自行安装 phpMyAdmin。官网地址为：https://www.phpmyadmin.net
可以直接在首页下载支持所有语言的版本：
或者进入下载页面自行选择版本：https://www.phpmyadmin.net/downloads/
下载
[root@ryan src]# wget https://files.phpmyadmin.net/phpMyAdmin/4.8.2/phpMyAdmin-4.8.2-all-languages.zip
解压到指定目录
[root@ryan src]# unzip /ucr/local/src/phpMyAdmin-4.8.-all-languages.zip -d /data/www/DiscuzX
复制配置文件
由于目录较长，最好先重命名一下：mv /data/www/phpMyAdmin/phpMyAdmin-4.8.2-all-languages /data/www/phpMyAdmin/phpMyAdmin
[root@ryan ~]# cd /data/www/DiscuzX/phpMyAdmin
[root@ryan phpMyAdmin]# cp config.sample.inc.php config.inc.php
[root@ryan phpMyAdmin]# vim config.inc.php
$cfg['blowfish_secret'] = 'ryan';#找到这一行，添加任意的值
保存退出。phpMyAdmin 的配置文件为：config.inc.php
启动 apache
[root@ryan phpMyAdmin]# apachectl -t
Syntax OK
[root@ryan phpMyAdmin]# apachectl start
访问管理页面
可以看出多国语言版支持的语言种类很多，可以根据自己需要选择，如下：
输入用户名和密码即可进入phpMyAdmin 管理后台。
附录