经过一段时间mysql资料收集，收货良多。接下来在进行在进行深入的学习。

环境win10 Ubuntu 18.04 LTS

安装：

dpkg -l |grep mysql
apt install mysql-server
service mysql stop
usermod -d /var/lib/mysql/ mysql
service mysql start
netstat -tap |grep mysql #注意这个在子系统里面看到任何输出，在win的资源管理器里面会有mysqld的进程，这个时候就已经启动了。

这个时候是没有密码的直接输入 mysql即可登录，在/etc/mysql/debian.cnf文件里面可以看到2个账户和密码

mysql的基础创建表，增删改查就不用多说了。主要操作实践几点：

mysql的备份和恢复
mysql的优化
高可用
一些高级特殊的用法

mysql的备份和恢复

create database backtest;
use backtest;

#准备测试表
create table user( 
`id` int(11) not null auto_increment, 
`name` varchar(32) not null, 
primary key (`id`)
) ENGINE=InnoDB default charset=utf8 comment='user table';
#查看表结构
desc user;
#新增一条数据
insert into user value (0, 'test');

#查询条数
select count(*) from user;
#查看是否开启binlog
show variables like 'log_bin';  
#如果为OFF的话，编辑/etc/mysql/my.cnf（你的可能是/etc/my.cnf根据自己实际的来），增加以下代码到[mysqld]下
server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30

#退出保存，然后再次运行上面的查询，可以看到变ON了。

mysqldump

mysqldump全量备份

mysqldump 命令参数详细解释：https://blog.csdn.net/h952520296/article/details/87343135
这个子系统Ubuntu的默认sock文件在：/var/run/mysqld/mysqld.sock，你需要用，可以自己去/etc/mysql/my.cnf修改
至于mysql.sock到底是什么，有什么用可以看下这篇文章：https://blog.csdn.net/kerwin612/article/details/8568206

//退出mysql
quit

备份命令

#临时测试目录，实际按自己需求更改
mkdir /tmp/backup 
#mysqld.sock文件使用自己实际的
mysqldump -S /var/run/mysqld/mysqld.sock -A -B -F -x --events --triggers --routines --master-data=2 > /tmp/backup/all_data-`date +%F--%U`.sql
#查看备份文件
ll -h /tmp/backup/

可以运行 mysqldump --help看下参数的使用
上述命令详细解释，更多参数查阅--help或者官方文档

-S, --socket=name   The socket file to use for connection. 指定socket文件

-A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected. 全部数据库

-B, --databases     Dump several databases. Note the difference in usage; in
                      this case no tables are given. All name arguments are
                      regarded as database names. 'USE db_name;' will be 指定数据库

-F, --flush-logs    Flush logs file in server before starting dump. Note that
                      if you dump many databases at once (using the option
                      --databases= or --all-databases), the logs will be
                      flushed for each database dumped. The exception is when
                      using --lock-all-tables or --master-data: in this case
                      the logs will be flushed only once, corresponding to the
                      moment all tables are locked. So if you want your dump
                      and the log flush to happen at the same exact moment you
                      should use --lock-all-tables or --master-data with
                      --flush-logs. 刷新日志，创建新的binlog日志，具体可以看下https://www.cnblogs.com/chuanzhang053/p/8709954.html这篇文章

-x, --lock-all-tables 
                      Locks all tables across all databases. This is achieved
                      by taking a global read lock for the duration of the
                      whole dump. Automatically turns --single-transaction and
                      --lock-tables off. 锁定表

-E, --events        Dump events. 

--triggers          Dump triggers for each dumped table. 导出触发器，触发器使用的比较少，可以看文章了解触发器的使用：https://www.cnblogs.com/fps2tao/p/10400936.html
                      (Defaults to on; use --skip-triggers to disable.)

-R, --routines      Dump stored routines (functions and procedures). 自定义函数导出

--master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.选项的作用就是将二进制的信息写入到输出文件中，在这里是写入到备份的sql文件中，参考：https://www.cnblogs.com/chuanzhang053/p/8710434.html

mysqldump增量备份

新增一条数据

mysql
user user;
insert into user value (0, 'test2');
quit;
#增量备份
mysqladmin -S /var/run/mysqld/mysqld.sock flush-logs   #增量备份前，先滚动一下二进制日志
#找到datadir
ps -ef|grep mysql
cd /var/lib/mysql/
cp `cat /var/lib/mysql/mysql-bin.index | tail -n 2 | head -n 1` /tmp/backup/   #备份二进制日志
ll /tmp/backup/  #里面多了个mysql-bin.000带数字的文件

数据恢复

破坏数据库

drop table user;

恢复，你自己的备份文件

source /tmp/backup/all_data-2019-11-13--45.sql

select * from user; #这里可能有些终端需要quit一下，再进mysql,然后看到里面有1条数据，但是新增的test2不存在了
# 恢复增量
quit

# 命令行执行
mysqlbinlog /tmp/backup/mysql-bin.000003 |mysql  #如果有用户名密码的 后面要改成 mysql -uroot -p
# 再次查看数据
mysql
select * from backtest.user; #此时可以看到2条数据已经恢复了

适合备份较小的数据，如果数据内容在10G内 mysqldump比较合适

lvm + binlog备份

什么是lv

LVM是逻辑盘卷管理（Logical Volume Manager）的简称，它是Linux环境下对磁盘分区进行管理的一种机制，LVM是建立在硬盘和分区之上的一个逻辑层，来提高磁盘分区管理的灵活性:https://baike.baidu.com/item/LVM

原理：

通过文件系统支持的快照功能对数据库进行备份。备份的原理是将所有的数据库文件放在同一分区中，然后对该分区执行快照工作，然后结合binlog的增量日志实现快照备份

操作

前提：
1.数据文件要在逻辑卷上；
2.此逻辑卷所在组必须有足够空间使用快照卷；
3.数据文件和事物日志要在同一个卷上

由于子系统里面lvm用不了，后面用实体机进行测试

备份流程如下：

1.创建逻辑卷
2.挂载
3.数据库修改datadir 和log_bin 迁移数据库
4.加读锁
5.flush logs 生成新的binlog日志
6.数据库目录做快照
7.解锁
8.挂载快照
9.cp或者mv备份快照文件到本地目录例如cp到 /backup/fullbak(后面恢复用这个路径)
10.删除快照卷
11.cat /path/to/master.info;查看当前二进制日志位置
12.mysqlbinlog --start-position= mysql-bin. >/backup/bak.sql 增量备份二进制日志

还原流程

1.cp /backup/fullbak 到你数据库datadir目录
2.重启mysql
3.set sql_log_bin=0;关闭二进制日志(为了后面导入增加不会产生新的binlog日志)
4.source /backup/bak.sql 导入binlog导出的sql增量
5.set sql_log_bin=1; 打开日志

完成还原

多增加几个数据,蠕虫复制创建测试数据,运行下面的数据多运行几遍

insert into user select 0, concat('test', `id`) from user;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

错误记录

刚安装完mysql8.0.11，连接时报错：
在navicat中报错client does not support authentication
在mysql workbench中报外部组件错误

解决步骤
使用如下命令：

mysql>  alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.60 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.18 sec)

深入mysql学习

安装：