Mysql实战

介绍

MySQL 是应用最广泛的开源关系数据库，是许多常见网站、应用程序和商业产品使用的主要关系数据存储。MySQL 有 20 多年的社区开发和支持历史，是一种可靠、稳定而安全的基于 SQL 的数据库管理系统。MySQL 数据库适用于各种使用案例，包括任务关键型应用程序、动态网站以及用于软件、硬件和设备的嵌入式数据库。

下载

官方下载地址

软件包列表：

• MySQL-client-5.6.24-1.el6.x86_64.rpm
• MySQL-server-5.6.24-1.el6.x86_64.rpm
• mysql-connector-java-5.1.27.tar.gz ： 操作系统选择Platform Independent（平台无关）

安装（Centos7）

1、卸载旧版本

$ rpm -qa|grep mysql                                  # 查看
mysql-libs-5.1.73-7.el6.x86_64
$ rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64      # 卸载

2、安装服务端

$ rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm   
$ cat /root/.mysql_secret                             # 查看安装产生的随机密码
$ service mysql status
$ service mysql start

3、安装客户端

$ rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm
$ mysql -uroot -pOEXaQuS8IWkG19Xs                     # 连接mysql服务端
mysql> SET PASSWORD=PASSWORD('000000');               #修改密码

4、user配置

mysql>use mysql;
mysql>select User, Host, Password from user;              # 查询User表
# 修改Host，可以远程访问
mysql>update user set host='%' where host='localhost';    
mysql>delete from user where Host='hadoop102';
mysql>delete from user where Host='127.0.0.1';
mysql>delete from user where Host='::1';
mysql>flush privileges;                                    # 刷新
mysql>quit;

5、配置文件

配置文件位置，Linux默认在/etc/my.cnf，如果找不到通过一下命令查找：

# 两种方式都可以查找my.cnf位置
$ locate my.cnf
mysql> select @@basedir;

修改配置文件：

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 开启binlog
log-bin=mysql-bin      
server-id=1
# 设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
# 开启binlog的库
binlog-do-db=db_name1
binlog-do-db=db_name2
# 数据存放位置
datadir=/var/lib/mysql
socket=/var/lib/mysql.sock
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-bin=mysql-bin 这个表示binlog日志的前缀是mysql-bin，以后生成的日志文件就是 mysql-bin.000001 的文件后面的数字按顺序生成，每次mysql重启或者到达单个文件大小的阈值时，新生一个文件，按顺序编号。

使用

TODO

原理

MYSQL执行顺序

MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

下面我们来具体分析一下查询处理的每一个阶段

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1。
2. ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6。
7. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9。
10. ORDER BY: 将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10。
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

SQL查询处理的步骤序号： 
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

Mysql的binlog

binlog介绍

MySQL的二进制日志可以说MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗。二进制有两个最重要的使用场景:

1. MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
2. 数据恢复，通过使用mysqlbinlog工具来使恢复数据。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

Binlog的分类

参考博客

binlog的格式也有三种：STATEMENT、ROW、MIXED。

• STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
  优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
  缺点：在某些情况下会导致master-slave中的数据不一致(比如：delete from t where a>=4 and t_modified<='2018-11-10' limit 1;在主库执行这个语句的时候，如果使用的是a索引，会删除(4,4,'2018-11-10')这条记录，如果使用的是t_modified的索引则会删除insert into t values(5,5,'2018-11-09');所以在执行这条sql语句的时候提示：
  Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
  由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的。
  sleep()函数， last_insert_id()，以及user-defined functions(udf)等也会出现问题)；
• ROW基于行的复制(row-based replication, RBR)格式：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。
  优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
  缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
• MIXED混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

可以看到ROW模式下，binlog日志中的begin和commit之间并没有sql语句。
在STATEMENT模式下，binlog日志中的begin和commit之间是一条sql语句。

事务隔离级别

详情见： 本地事务.md

主从同步

主服务器配置

配置：

$ vim my.cnf
#主数据库端ID号
server_id = 1           
 #开启二进制日志                  
log-bin = mysql-bin    
#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可                  
binlog-do-db = db        
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中                 
log-slave-updates                        
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) 
sync_binlog = 1                    
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_offset = 1           
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1            
#二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除  
expire_logs_days = 7                    
#将函数复制到slave  
log_bin_trust_function_creators = 1  

授权：

# 允许从服务器同步数据的账户 user 123456
mysql> GRANT replication slave ON *.* TO user@'192.168.64.23' IDENTIFIED BY '123456';
mysql> show master status; # 查看主服务器状态，获取到日志名和偏移量

show master status获取到日志名和偏移量，在从数据库启动后从这个点开始进行数据恢复。

从服务器配置

配置：

$ vim my.cnf
server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
#log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0        
#指定slave要复制哪个库
replicate-do-db = db         
#MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60                    
log_bin_trust_function_creators = 1

执行同步：

mysql> change master to master_user='user',   # 授权用户
     > master_password='123456',
     > master_host='192.168.64.23',           # 主服务器地址
     > master_log_file='mysql-bin.000003',    # 主服务器使用的二进制日志（）
     > master_log_pos='257';                  # 主服务器日志偏移量
mysql> start slave;                 # 开启同步    

mysql> show slave status\G;         # 查看从服务器的内容
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.111
                  Master_User: account
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000033
          Read_Master_Log_Pos: 337523
               Relay_Log_File: db2-relay-bin.000002
                Relay_Log_Pos: 337686
        Relay_Master_Log_File: mysql-bin.000033
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
          ...
# Slave_IO_Running及Slave_SQL_Running进程必须正常运行，即Yes状态，否则说明同步失败

其他配置

开启远程访问权限

一、开启mysql远程访问

授予用户user 密码 passwd 所有权限 所有主机IP可访问

• 授权语句：Grant <权限> on 表名[(列名)] to 用户 With grant option或 GRANT <权限> ON <数据对象> FROM <数据库用户>

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;

• ALL PRIVILEGES表示所有权限，*.*表示所有数据库和表，%表示所有IP，WITH GRANT OPTION授予授权权限,如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“。
  只能访问数据库gogs的所有权

GRANT ALL PRIVILEGES ON gogs.* TO 'user2'@'%' IDENTIFIED BY 'passwd2' WITH GRANT OPTION;

分别授予用户所有主机IP可访问，分别拥有增删改查权限

GRANT select ON *.* TO 'user1'@'%' IDENTIFIED BY 'passwd1' WITH GRANT OPTION;
GRANT insert ON *.* TO 'user2'@'%' IDENTIFIED BY 'passwd2' WITH GRANT OPTION;
GRANT updata ON *.* TO 'user3'@'%' IDENTIFIED BY 'passwd3' WITH GRANT OPTION;
GRANT delete ON *.* TO 'user4'@'%' IDENTIFIED BY 'passwd4' WITH GRANT OPTION;

查询用户具有的权限,因为只给了查询权限，所以只有Select_priv: Y。

mysql> GRANT select ON *.* TO 'user'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select * from mysql.user where user='user'\G;
*************************** 1. row ***************************
                  Host: %
                  User: user
           Select_priv: Y
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
       
1 row in set (0.00 sec)

ERROR: 
No query specified

二、撤销已经赋予给 MySQL 用户权限的权限。

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

grant all on *.* to dba@localhost;

revoke all on *.* from dba@localhost; 

grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

三、对数据库开启只读权限，用于数据库热备份

1、 对于MySQL单实例数据库和master库，如果需要设置为只读状态，需要进行如下操作和设置：
将MySQL设置为只读状态的命令:

mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";

将MySQL从只读状态设置为读写状态的命令:

mysql> unlock tables;
mysql> set global read_only=0;

2)、对于需要保证master-slave主从同步的salve库
将slave从库设置为只读状态，需要执行的命令为:

mysql> set global read_only=1;

将salve库从只读状态变为读写状态，需要执行的命令是:

mysql> set global read_only=0;

授权

授权格式：

GRANT <privileges> ON <dbName.tableName>  TO <user> [IDENTIFIED BY "<password>"]  [WITH GRANT OPTION];

授权参数讲解：

privilegesCode表示授予的权限类型，常用的有以下几种类型：

• all privileges：所有权限。
• select：读取权限。
• delete：删除权限。
• update：更新权限。
• create：创建权限。
• drop：删除数据库、数据表权限。

dbName.tableName表示授予权限的具体库或表，常用的有以下几种选项：

• *.*：授予该数据库服务器所有数据库的权限。

• dbName.*：授予dbName数据库所有表的权限。

• dbName.dbTable：授予数据库dbName中dbTable表的权限。

username@host 表示授予的用户以及允许该用户登录的IP地址。其中Host有以下几种类型：

• localhost：只允许该用户在本地登录，不能远程登录。
• %：允许在除本机之外的任何一台机器远程登录。
• 192.168.52.32：具体的IP表示只允许该用户从特定IP登录。

password指定该用户登录时的面。

flush privileges表示刷新权限变更。

1、查看所有用户以及授权主机

mysql> select User,Host from mysql.user;
+--------+-----------+
| User   | Host      |
+--------+-----------+
| user2  | %         |
| zabbix | %         |
| root   | localhost |
+--------+-----------+
5 rows in set (0.00 sec)

2、查看某个用户的授权情况

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

或者

mysql> show grants for 'zabbix'@'%';
+------------------------------------+
| Grants for zabbix@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'%' |
+------------------------------------+
1 row in set (0.00 sec)

3、授权

• 授权所有权限

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;

• 授权单个gogs库权限

GRANT ALL PRIVILEGES ON gogs.* TO 'user2'@'%' IDENTIFIED BY 'passwd2';

• 授权单个user表权限

GRANT ALL PRIVILEGES ON gogs.user TO 'user2'@'%' IDENTIFIED BY 'passwd2' WITH GRANT OPTION;

• 单独授权增删改查权限，而不是所有权限

grant select, insert, update, delete on gogs.* to user@'%'；

• 授权某个固定主机权限

grant select, insert, update, delete on gogs.* to user@'172.16.3.200'；

4、取消授权

revoke ALL PRIVILEGES ON *.* from 'user'@'%';
flush  privileges;

5、删除用户

drop user user@"%";

快速到出大量数据

一、导出数据

1、mysqldump

2、重命名压缩

3、scp传输

$ scp msn.sql.zip root@172.105.xx.xx:/root/

二、导入数据

1、解压

$ unzip msn.sql.zip

2、连接数据库

mysql -umsn_block -p password
mysql -umsn_mall -p  password

3、设置参数

#设置参数
set autocommit=0;
use msn_block;
START TRANSACTION;
#导入数据库
source /root/msn.sql;
#提交事务
COMMIT;

单机备份

mybackup.conf

username=数据库用户名
password=数据库密码
backupnode=full
（备份方式）

myback.sh

#/usr/bin/env bash
BASEDIR="/home"
username=`cat ${BASEDIR}/mybackup.conf | grep "username" | awk -F '=' '{print $2}'`
password=`cat ${BASEDIR}/mybackup.conf | grep "password" | awk -F '=' '{print $2}'`
time=` date +%Y_%m_%d_%H_%M_%S `
db_name=new_retail_back
dbname=pro_wallet_back
#db=fitment_back
#dbb=wjjx_pro_wallet_back
dbyqf=yqf_back
dbdmk=duoduoke
dbgw=buhuo3
dbwz=web_duomaike
dbbhpro=buhuopro
backupdir="/mnt/data/mysql"
echo "username and password"
echo ${username}
echo ${password}
echo "begin to backup"
#mysqldump -hlocalhost -u${username} -p${password} -F --databases new_retail | gzip > $backupdir/$db_name$time.sql.gz
mysqldump -hlocalhost -u${username} -p${password} --databases pro_wallet | gzip > $backupdir/$dbname$time.sql.gz
mysqldump -hlocalhost -u${username} -p${password} --databases yqf | gzip > $backupdir/$dbyqf$time.sql.gz
mysqldump -hlocalhost -u${username} -p${password} --databases duoduoke | gzip > $backupdir/$dbdmk$time.sql.gz
mysqldump -hlocalhost -u${username} -p${password} --databases buhuo3 | gzip > $backupdir/$dbgw$time.sql.gz
mysqldump -hlocalhost -u${username} -p${password} --databases web_duomaike | gzip > $backupdir/$dbwz$time.sql.gz
mysqldump -hlocalhost -u${username} -p${password} --databases buhuopro | gzip > $backupdir/$dbbhpro$time.sql.gz
#删除三天之前的备份
#find /home/data/mysql -name $db_name"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
find /mnt/data/mysql -name $dbname"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
find /mnt/data/mysql -name $dbyqf"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
find /mnt/data/mysql -name $dbdmk"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
find /mnt/data/mysql -name $dbgw"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
find /mnt/data/mysql -name $dbwz"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
find /mnt/data/mysql -name $dbbhpro"*.sql.gz" -type f -mtime 2 -exec rm -rf {} \; > /dev/null 2>&1
#删除一分钟之前的备份
#find $backup_dir -name $db_name"*.sql.gz" -type f -mmin +1 -exec rm -rf {} \; > /dev/null 2>&1

异地备份
remote_backup.sh

#！bin/bash
#The author : RoES
#date:2019/6/3
#Used for:mysql remote_backup
#用于数据库异地备份

basefile=/mnt/data/mysql/
ip=112.74.53.175

date
echo "becoming remote_backup"
echo "---start backup-------"
rsync -avz /mnt/data/mysql/* root@112.74.53.175:/home/data/mysql/
echo "backup complete"
exit $?

binlog日志

1、显示mysqlbinlog日志是否开启，以及存储位置

show variables like 'log_%';

2、查看所有binlog日志列表

mysql> show master logs;

3、查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;

4、刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

5、重置(清空)所有binlog日志

此命令切勿随便执行。

mysql> reset master;