写在前面 这则笔记开始整理mysql的基本配置和使用知识,主要包括以下内容：
初次登陆及基本设置
用户管理
常用语句
数据库备份和恢复
mysql主从配置
注： 部分测试是在mariaDB当中进行的。
初次登陆及基本设置 之前在lnmp环境的搭建当中已经分别整理过mariaDB、mysql的安装方法，参见这里 。
修改环境变量 测试环境使用的是yum安装mariaDB，其安装目录为/usr/bin/mysql。如果不清楚，可以使用find命令查一下：
1 2 3 4 5 6 7 8 9 10 11 12 [root@stevey ~]# find / -name 'mysql' /etc/logrotate.d/mysql /etc/rc.d/init.d/mysql /etc/selinux/targeted/active/modules/100/mysql /var/lib/mysql /var/lib/mysql/mysql /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/local/src/php-7.2.6/travis/ext/mysql [root@stevey ~]# which mysql /usr/bin/mysql
如果采用源码包安装的mysql，一般安装目录在/usr/local/mysql/ ，因此在执行命令时需采用绝对路径，非常不方便。解决方法有两种：
1 2 3 4 5 6 7 vim /etc/profile //增加一行： export PATH=$PATH:/usr/local/mysql/bin/ //保存退出后，执行以下命令生效 source /etc/profile
1 ln -s /usr/local/mysql/bin/mysql /usr/bin/
初次登陆 1 mysql [-u username] [-h host] [-p password] [database]
其中，
由于是本地虚拟机安装mysql，初次登陆也设置密码，因此只需要输入如下命令即可。
设置mysql root 密码(三种方式)
1 mysqladmin –u root password 123456 //密码设定为123456
1 2 3 [root@stevey test]# mysql MariaDB [(none)]> set password for root@localhost = password('PASSWORD'); MariaDB [(none)]> flush privileges; //执行刷新命令后生效
1 2 MariaDB [(none)]> update mysql.user set password = password('PASSWORD') where user='root'; MariaDB [(none)]> flush privileges; //执行刷新命令后生效
用户授权
1 2 3 4 5 //all 表示所有权限（读、写、执行）； //`*.* ` 前一个星号表示所有的数据库，后一个表示所有的表； //username 表示用户名； //identified by 后面跟的是密码； grant all on *.* to username identified by 'abcdef';
1 2 //hostip是远程主机的ip grant all on db1.* to 'username'@'hostIP' identified by 'abcdef';
查看当前用户 1 2 3 4 5 6 7 8 9 MariaDB [(none)]> select User,Host,Password from mysql.user; +------+-----------+-------------------------------------------+ | User | Host | Password | +------+-----------+-------------------------------------------+ | root | localhost | *2E31C5EB30A0D665E861AB5277A2508FB7E7C16A | | root | 127.0.0.1 | *2E31C5EB30A0D665E861AB5277A2508FB7E7C16A | | root | ::1 | *2E31C5EB30A0D665E861AB5277A2508FB7E7C16A | +------+-----------+-------------------------------------------+ 3 rows in set (0.13 sec)
重置root密码
1 2 3 4 vim /etc/my.cnf //在[mysql]下一行增加如下内容： skip-grant
1 /etc/init.d/mysqld restart
1 2 3 4 5 MariaDB [(none)]> use mysql; //用户名密码存在user表里，而user表存在mysql这个库里，进入mysql，记得加分号 MariaDB [(none)]> select * from user; //查看user表 MariaDB [(none)]> select password from user where user='root' ; //查询语句查询密码表。加密的字符串是password这个函数生成
1 2 3 4 vim /etc/my.cnf //删除如下内容行或 skip-grant
mysql常用命令
1 2 3 4 5 6 7 8 9 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.03 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 MariaDB [mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | column_stats | | columns_priv | | db | | event | | func | | general_log | | gtid_slave_pos | | help_category | | help_keyword | | help_relation | | help_topic | | host | | index_stats | | innodb_index_stats | | innodb_table_stats | | plugin | | proc | | procs_priv | | proxies_priv | | roles_mapping | | servers | | slow_log | | table_stats | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 30 rows in set (0.00 sec)
1 show create table user\G;
1 2 3 4 5 6 7 MariaDB [mysql]> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
1 2 3 4 5 6 7 MariaDB [mysql]> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
1 2 MariaDB [mysql]> create database db1; //创建名为db1的库 Query OK, 1 row affected (0.00 sec)
1 2 3 4 5 6 7 //sql语句以分号结尾，下面实际上是将两个语句放在一行书写 //id 和 name 使用的是反引号，tab键上面的 //int(4)表示这个表仅包括整数，括号里的4表示最大为4位数 //char(40)表示固定长度（40位）的字符数；如果是可变长度的字符数，用varchar（n）表示 MariaDB [db1]> use db1; create table t1(`id` int(4), `name` char(40)); Database changed Query OK, 0 rows affected (0.07 sec)
1 2 3 4 5 6 7 MariaDB [db1]> select version(); +-----------------+ | version() | +-----------------+ | 10.2.15-MariaDB | +-----------------+ 1 row in set (0.04 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 //查看参数 show variables; //查看指定的参数 show variables like 'max_connect%'; //示例 MariaDB [db1]> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 //设定参数 MariaDB [db1]> set global max_connect_errors=1000; Query OK, 0 rows affected (0.05 sec) //查看参数设定的结果 MariaDB [db1]> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。
show processlist 默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 MariaDB [db1]> show processlist; +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost | db1 | Query | 0 | init | show processlist | 0.000 | +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.00 sec) MariaDB [db1]> show full processlist; +----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost | db1 | Query | 0 | init | show full processlist | 0.000 | +----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+ 6 rows in set (0.00 sec)
查询结果的说明：
Id: 就是这个线程的唯一标识，show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。
User: 就是指启动这个线程的用户。
Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command: 是指此刻该线程正在执行的命令，这个选项需要和state结合起来看。
Time: 表示该线程处于当前状态的时间。
State: 线程的状态，和 Command 对应，下面单独解释。
mysql列出的状态State主要有以下几种：
Checking table：正在检查数据表；
Closing tables：正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中；
Connect Out：复制从服务器正在连接主服务器；
Copying to tmp table on disk：由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table：正在创建临时表以存放部分查询结果；
deleting from main table：服务器正在执行多表删除中的第一部分，刚删除第一个表；
deleting from reference tables：服务器正在执行多表删除中的第二部分，正在删除其他表的记录；
Flushing tables；正在执行FLUSH TABLES，等待其他线程关闭数据表；
Killed；发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效；
Locked；被其他查询锁住了。
Sending data：正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group：正在为GROUP BY做排序。
Sorting for order：正在为ORDER BY做排序。
Opening tables：这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates：正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table：获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting：修复指令正在排序以创建索引。
Repair with keycache：修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update：正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping：正在等待客户端发送新请求.
System lock：正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
Upgrading lock：INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating：正在搜索匹配的记录，并且修改它们。
User Lock：正在等待GET_LOCK()。
Waiting for tables：该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert：INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
mysql用户管理 添加用户并授权 添加用户 命令行格式：
1 create user [username] identified by 'password';
示例：
1 2 MariaDB [(none)]> create user testUser1 identified by '123456'; Query OK, 0 rows affected (0.04 sec)
授权 仅仅添加用户还是不够的，还需要授权，该用户才能对根据授权进行相关操作。
命令行格式：
1 grant privilegesCode on dbName.tableName to username@host identified by "password";
privilegesCode表示授予的权限类型，常用类型包括：
all privileges：所有权限；
select：读取权限；
delete：删除权限；
update：更新权限；
create：创建权限；
drop：删除数据库、数据表权限；
dbName.tableName表示授予权限的具体库或表，常用的有以下几种选项：
username@host表示授予的用户以及允许该用户登录的IP地址。主要有一下几种类型：
刷新权限变更 1 2 MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.03 sec)
修改密码 1 2 3 4 5 //修改密码 update mysql.user set password = password('123456') where user = 'testUser1' and host = '%'; //更新配置 flush privileges;
删除用户 常用sql语句 查询语句 第一种：
1 2 //查询mysql数据库中的user表一共有多少行 select count(*) from mysql.user;
第二种：
1 2 3 4 5 6 7 8 9 10 11 //查询mysql数据库的db表中所有的数据 select * from mysql.db; //查询单个关键词 select username from mysql.db; //查询多个关键词，之间用逗号隔开 select username, passwd from mysql.db; //使用通配符进行查询 select * from mysql.db where host like '192.168.%';
插入及删除
1 2 3 4 5 6 7 8 9 10 11 12 //插入一行，共两列，第一列为1，第二列为abc MariaDB [(none)]> insert db1.t1 values(1, 'abc'); Query OK, 1 row affected (0.11 sec) //查看当前表，校验上一步是否成功 MariaDB [(none)]> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.02 sec)
1 2 3 4 5 6 7 8 9 10 11 MariaDB [(none)]> update db1.t1 set name='abcdef' where id=1; Query OK, 1 row affected (0.14 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [(none)]> select * from db1.t1; +------+--------+ | id | name | +------+--------+ | 1 | abcdef | +------+--------+ 1 row in set (0.00 sec)
清空某个表的数据 1 2 3 4 5 6 7 //清空db1.t1表 MariaDB [(none)]> truncate table db1.t1; Query OK, 0 rows affected (0.14 sec) //查看结果 MariaDB [(none)]> select * from db1.t1; Empty set (0.00 sec)
删除表 1 2 3 4 5 6 7 //删除db1.t1表 MariaDB [(none)]> drop table db1.t1; Query OK, 0 rows affected (0.06 sec) //校验结果，db1.t1不存在说明已删除 MariaDB [(none)]> select * from db1.t1; ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
删除数据库
数据库的备份和恢复 mysqldump命令：备份
1 2 3 4 5 6 7 8 9 10 11 12 //备份所有数据库： mysqldump -ujack -p --all-databases > /tmp/all.sql //备份指定数据库test： mysqldump -ujack -p test > /tmp/test.sql //备份指定的表。可以包括多个表，以空格分隔 //备份books数据库中的orders和users表 mysqldump -ujack -p books orders users > /tmp/key.sql //备份表结构（不备份数据本身）： mysqldump -ujack -p --no-data books > /tmp/books.sql
1 mysql -u root -p'123456' mysql < /tmp/mysql.sql;
实验测试
文件准备，先创建一个数据库（db2），其中有表t1，t1中包含两行数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 MariaDB [(none)]> create database db2; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use db2; create table t1(`id` int(4), `name` varchar(40)); Database changed Query OK, 0 rows affected (0.03 sec) MariaDB [db2]> insert db2.t1 values(1, 'abc');insert db2.t1 values(2, 'def'); Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) MariaDB [db2]> select * from db2.t1; +------+------+ | id | name | +------+------+ | 1 | abc | | 2 | def | +------+------+ 2 rows in set (0.00 sec)
1 mysqldump -u root -p'123456' db2 > /tmp/mysql.sql;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | db2 | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> drop database db2; Query OK, 1 row affected (0.03 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
1 2 3 //直接回复显示错误 [root@stevey ~]# mysql -u root -p'holypoter5500' db2 < /tmp/mysql.sql ERROR 1049 (42000): Unknown database 'db2'
1 mysql -u root -p'123456' db2 < /tmp/mysql.sql;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 [root@stevey ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 26 Server version: 10.2.15-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select * from db1.t1; ERROR 1146 (42S02): Table 'db1.t1' doesn't exist MariaDB [(none)]> select * from db2.t1; +------+------+ | id | name | +------+------+ | 1 | abc | | 2 | def | +------+------+ 2 rows in set (0.00 sec)
mysql主从配置 主从配置原理 所谓mysql主从配置，就是假设有两台服务器A和B，其中A服务器为master，B服务器为slave，在A服务器master写数据时，会自动同步到B服务器slave，两台服务数据同步，保持一致。
两台服务器的通讯过程大致如下：
master将更改操作记录到binlog里;
slave将master的binlog事件(sql语句)同步到本机上并记录在relaylog里;
slave根据relaylog里面的sql语句按顺序执行;
实现步骤 实验在两台虚拟机上完成，其中：
安装的mysql版本一致，均为5.6
1 2 [root@local-linux02 ftp]# mysql --version mysql Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using EditLine wrapper
准备工作： 先在master服务器上创建一个数据库 syncMaster
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 [root@local-linux02 ~]# mysql -u root -p Enter password: mysql> create database syncMaster; Query OK, 1 row affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | syncMaster | | test | +--------------------+ 5 rows in set (0.02 sec)
master服务器配置：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 vi /etc/my.cnf //增加以下配置内容： //主数据库端id号 server_id = 100 //开启二进制日志 log-bin = mysql-bin //需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可 binlog-do-db = syncMaster //slave服务器将master更新记入到自身自己的二进制日志文件中 log-slave-updates //定义每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) sync_binlog = 100 //一般用在主主同步中，用来错开自增值, 防止键值冲突 auto_increment_offset = 1 //一般用在主主同步中，用来错开自增值, 防止键值冲突 auto_increment_increment = 1 //二进制日志自动删除的天数，默认值为0,表示“没有自动删除” expire_logs_days = 7 //将函数复制到slave log_bin_trust_function_creators = 1
1 2 3 4 5 6 //账户名为syncSlave，密码为123456 mysql> grant replication slave on *.* to 'syncSlave'@'172.16.155.128' identified by '123456'; Query OK, 0 rows affected (0.05 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)
slave服务器配置： 修改配置文件
1 2 3 4 5 6 7 8 9 10 11 vi /etc/my.cnf //修改如下配置 server_id = 101 log-bin = mysql-bin log-slave-updates sync_binlog = 101 innodb_flush_log_at_trx_commit = 0 replicate-do-db = syncMaster //指定slave要复制哪个库 slave-net-timeout = 60 //网络等待时间 log_bin_trust_function_creators = 1
执行同步命令
1 2 3 4 5 mysql> change master to master_host='172.16.155.132',master_user='syncSlave',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)
查看从服务器状态
1 mysql> show slave status\G;
TroubleShooting 连接错误
这里本该有两个yes才对，但是Slave_IO_Running: No，显然出错了。
排错过程：
1 2 3 4 5 6 7 mysql> show variables like 'log_error'; +---------------+-------------------------------+ | Variable_name | Value | +---------------+-------------------------------+ | log_error | /data/mysql/local-linux01.err | +---------------+-------------------------------+ 1 row in set (0.02 sec)
1 [root@local-linux01 mysql]# tail /data/mysql/local-linux01.err
重点是这句 The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work，意思是说主从服务器用了同样的server ID，这样是不行的。
1 2 3 4 5 6 7 mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 128 | +---------------+-------+ 1 row in set (0.00 sec)
slave服务器登陆mysql，结束之前的slave进程,重新授权，再启动slave进程
1 2 3 4 5 6 7 8 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to master_host='172.16.155.132',master_user='syncSlave',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
主从服务器测试： 让我们在主服务器数据库做一些更改，然后在从服务器上看看效果。
主服务器端操作 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 mysql> use syncMaster; Database changed mysql> create table test1(`id` int(4), `name` char(40)); Query OK, 0 rows affected (0.30 sec) mysql> INSERT INTO testDB.t1 values (1,'abc'); Query OK, 1 row affected (0.01 sec); mysql> show tables; +----------------------+ | Tables_in_syncMaster | +----------------------+ | test1 | +----------------------+ 1 row in set (0.00 sec) mysql> select * from syncMaster.test1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec)
从服务器Mysql查询： 1 2 3 4 5 6 7 mysql> show tables; +----------------------+ | Tables_in_syncMaster | +----------------------+ | test1 | +----------------------+ 1 row in set (0.00 sec)
参考资料
