MySQL

1、增加程序用户

useradd -M -s /sbin/nologin mysql

2、解压源码包

tar xf mysql-5.5.22.tar.gz -C /usr/src/

3、cmake配置、编译、安装

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSET=all && make && make install

4、优化操作
修改mysql安装目录的属主属组

 chown -R mysql /usr/local/mysql/

创建修改my.cnf配置文件

cp support-files/my-medium.cnf /etc/my.cnf

5、添加服务

cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld 
chkconfig mysqld --add
chkconfig mysqld --list

6、添加MySQL执行路径到环境变量

echo "export PATH=$PATH:/usr/local/mysql/bin">>/etc/profile
source /etc/profile

7、执行mysql_install_db初始化脚本

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

–user=mysql 指定用户
–basedir=/usr/local/mysql/ 指定安装目录
–datadir=/usr/local/mysql/data/ 指定数据目录
8、设置数据库用户名、密码

mysqladmin -uroot password wasd

mysql -u 指定用户名
-h 指定主机（host）
-p 指定密码
-P 指定端口号
9、table补全
修改配置文件

vim /etc/my.cnf

10.用户权限

mysql> grant all privileges on *.* to 'username'@'%(ipaddres)' identified by 'password' with grant option;

• 查看当前用户权限 show grants;
• 查看特定用户权限 show grants for ‘username’@‘ipaddres’
• 撤销用户权限 revoke select on 库.表 from ‘username’@‘ip’
• 刷新 flush privileges
• 显示当前连接用户 select user();
• 显示当前时间 select now();
• 显示当前用户和时间 select user(),now();
• 显示服务器错误信息 show errors;

MySQL通配符：
任意单个字符 ： _
任意长度任意字符 ： %
常见权限列表

用户权限 参考 https://blog.csdn.net/paullinjie/article/details/80343823

二、SQL语句

三、MySQL日志管理

日志类型：
1.错误日志
包含启动和停止时，运行过程中发生的任何错误信息，默认安装目录/usr/local/mysql/data下的“主机名.err ”
可以在登录时使用“–log-error=文件路径及文件名”的方式指定mysqld保存错误日志位置；或修改配置文件/etc/my.cnf [mysqld]下方添加 “log-error=文件路径” ，重启服务。

2.通用日志
MySQL所有连接和语句都会记录。默认关闭此项日志记录，一般用作调试，明示开启会占用大量 磁盘空间。
可以在登录时使用“–log=文件路径及文件名”或“-l 文件路径及文件名”； 修改配置文件 在[mysqld]下添加’log=ON’和 ‘log=文件路径及文件名’

3.二进制日志（记录SQL语句）做高可用、主从复制、MHA
二进制日志包含所有更新了的数据或已经潜在更新了数据的所有语句，记录数据的更改。主要目的是在恢复时可以最大可能恢复数据库。默认开启。
默认路径在/usr/local/mysql/data下以mysql-bin开头的二进制日志
登录时用‘–log-bin=文件路径及文件名前缀’；修改配置文件log-bin=文件路径及文件名 修改日志存放位置
可使用mysqlbinlog命令查看二进制日志文件
4.慢查询日志
记录所有执行时间超过long_query_time秒的SQL语句，可用于找到执行时间长的查询，以便优化。默认关闭。
登录时使用‘–long-slow-queries[=文件路径及文件名]’选项开启，在/usr/local/mysql/data下生成‘主机名-slow.log’； 配置文件修改 [mysqld]下添加 ‘long_query_time’默认单位（秒）和’log-slow-queries=文件路径及文件名’

四、MySQL备份与恢复

一.数据库备份分类

物理备份：对数据库的物理文件进行备份
			1>冷备份：关闭数据库的时候进行
			2>热备份：数据库运行状态下进行备份，依赖与于数据库日志文件
			3>温备份：对数据库锁定表格（不可写入但可读）的状态下进行备份

逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份

全备份：每次对数据进行完整的备份
对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库， 是差异备份与增量备份的基础。
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长

差异备份：备份那些自从上次完全备份之后被修改过的所有文件备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份 以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

二.MySQL 完全备份操作

1、使用专用备份工具 mysqldump

MySQL 自带的备份工具，相当方便对 MySQL 进行备份。通过该命令工具可以将制定的库、表或全部的库导出为 SQL 脚本，在需要恢复时可进行数据恢复。

（1） 对单个库进行完全备份
格式：mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名示例：
[root@mysql ~]# mkdir /backup
[root@mysql ~]# mysqldump -uroot -p123123 auth > /backup/auth-$(date +%Y%m%d).sql

（2） 对多个库进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] --databases 库名 1 [库名 2]… > /备份路径/
备份文件名
示例：
[root@mysql ~]# mysqldump -uroot -p123123 --databases mysql auth >
/backup/mysql+auth-$(date +%Y%m%d).sql

（3） 对所有库进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名示例：
[root@mysql ~]# mysqldump -uroot -p123123 --opt --all-databases >/backup/mysql_all.$(date
+%Y%m%d).sql //–opt 加快备份速度，当备份数据量大时使用

（4） 对表进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名示例：
[root@mysql ~]# mysqldump -uroot -p123123 auth user >/backup/auth_user-$(date
+%Y%m%d).sql

（5） 对表结构的备份
格式：mysqldump -u 用户名 -p [密码] -d 数据库名 表名 > /备份路径/备份文件名示例：

[root@mysql ~]# mysqldump -uroot -p123123 -d mysql user >/backup/desc_mysql_user-$(date
+%Y%m%d).sql

2.使用 mysqldump 备份后，恢复数据库

1、source 命令
执行 source 备份 sql 脚本路径
示例：

mysql> show databases;
++
| Database	|
++
| information_schema |
| auth	|
| mysql	|
| performance_schema |
| test	|
++
5 rows in set (0.00 sec)
mysql> drop database auth;
Query OK, 1 row affected (0.05 sec)
mysql> source /backup/备份文件.sql 
mysql> show databases;
++
| Database	|
++
| information_schema |
| auth	|
| mysql	|
| performance_schema |
| test	|
++

2.mysql 命令
格式：mysql -u 用户名 -p [密码] < 库备份脚本的路径
mysql -u 用户名 -p [密码] 库名 < 表备份脚本的路径
示例：

[root@mysql ~]# mysql -uroot -p123123 -e 'show databases;'
++
| Database	|
++
| information_schema |
| auth	|
| mysql	|
| performance_schema |
[root@mysql ~]# mysql -uroot -p123123 -e 'drop database auth;'
[root@mysql ~]# mysql -uroot -p123123 < /backup/mysql_all.20160505.sql 
 [root@mysql ~]# mysql -uroot -p123123 -e 'show databases;'
++
| Database	|
++
| information_schema |
| auth	|
| mysql	|
| performance_schema |
| test	|
++

[root@mysql ~]# mysql -uroot -p123123 -e 'drop table auth.user;'
[root@mysql ~]# mysql -uroot -p123123 auth< /backup/auth_user-20160505.sql [root@mysql ~]# mysql -uroot -p123123 -e 'select * from auth.user;'
+++
| name	| ID	|
+++
| amber |	123 |
+++

三.MySQL 数据库备份脚本

[root@client ~]# vim /opt/bakmysql.sh
客户端备份脚本

#!/bin/bash
#MySQL 数据库备份脚本
#设置登录变量
MY_USER="admin" MY_PASS="123123" MY_HOST="192.168.1.108"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
#设置备份的数据库
MY_DB1="auth" MY_DB2="client"
#定义备份路径、工具、时间、文件名
BF_DIR="/backup" BF_CMD="/usr/bin/mysqldump" BF_TIME=$(date +%Y%m%d-%H%M) NAME_1="$MY_DB1-$BF_TIME" NAME_2="$MY_DB2-$BF_TIME"
#备份为.sql 脚本，然后打包压缩(打包后删除原文件)
[ -d $BF_DIR ] || mkdir -p $BF_DIR cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB1 > $NAME_1.sql
$BF_CMD $MY_CONN --databases $MY_DB2 > $NAME_2.sql
/bin/tar zcf $NAME_1.tar.gz $NAME_1.sql --remove &>/dev/null
/bin/tar zcf $NAME_2.tar.gz $NAME_2.sql --remove &>/dev/null

客户端恢复脚本

#!/bin/bash
#恢复 MySQL 数据库数据脚本
#设置变量
MY_USER="admin" MY_PASS="123123" MY_HOST="192.168.1.108"
BF_DIR="/backup" mkdir .aaa
ls $BF_DIR |column -t > .aaa/db_list
awk -F'-' '{print $2}' .aaa/db_list > .aaa/dt.txt
read -p "请指定要恢复数据库的日期(YYYYMMDD):" dt if [ $dt -ge 20160501 ] && [ $dt -le 20160601 ];then
grep "$dt" .aaa/dt.txt &>/dev/null if [ $? -ne 0 ];then
echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"
else
echo "搜索到的可恢复数据库如下:" awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list
read -p "请选择您要恢复数据库的编号: " nb
nm=$(awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list |awk /$nb/'{print $2}') echo "现在开始恢复数据库:$nm 到$dt"
cd $BF_DIR
onm=$(ls |grep "$nm-$dt") mkdir .bbb
tar xf $onm -C .bbb
mysql -u$MY_USER -p$MY_PASS -h$MY_HOST $nm < .bbb/* echo "$nm 已经恢复到$dt"
rm -rf .bbb cd - &>/dev/null
rm -rf .aaa fi
else
echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"
fi

四.MySQL 增量备份与恢复

1.增量备份概念

使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。
1、增量备份的特点：
没有重复数据，备份量不大，时间短
恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。
MySQL 没有提供直接的增量备份办法，可以通过MySQL 提供的二进制日志（binary logs） 间接实现增量备份。

2、MySQL 二进制日志对备份的意义：
二进制日志保存了所有更新或者可能更新数据库的操作。
二进制日志在启动MySQL 服务器后开始记录，并在文件达到 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建新的日志文件

2.增量恢复

1.一般的恢复：备份的二进制日志内容全部恢复
格式：

`mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码`

（2） 基于时间点的恢复：便于跳过某个发生错误的时间点实现数据恢复
格式：
从日志开头截止到某个时间点的恢复：

mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql
-u 用户名 -p 密码

从某个时间点到日志结尾的恢复：

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql
-u 用户名 -p 密码

从某个时间点到某个时间点的恢复：

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

（3） 基于位置的恢复：可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加精准
格式：

mysqlbinlog --stop-position=’操作 id’  二进制日志  |mysql -u 用户名  -p 密码
mysqlbinlog --start-position=’操作 id’  二进制日志  |mysql -u 用户名  -p 密码

·根据数据更新频繁，则应该较为频繁的备份
·数据重要，则在有适当更新时进行备份
·在数据库压力小的时段进行备份，如一周一次完全备份，然后每天进行增量备份
·中小公司，全备一般可一天一次
·大公司可每周进行一次全备，每天进行一次增量备份

五、MySQL索引与事务

一、数据库索引概述
1、数据库索引
在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所有数据。数据库的索引是某个表中一列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单。

2、索引的作用
（1） 设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率，特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成千倍。
（2） 可以降低数据库的 IO 成本，并且索引还可以降低数据库的排序成本。
（3） 通过创建唯一性索引保证数据表数据的唯一性，可以加快表与表之间的连接。
（4） 在使用分组和排序时，可大大减少分组和排序时间。
3、索引的分类
（1） 普通索引
这是最基本的索引类型，而且没有唯一性之类的限制
（2） 唯一性索引
与普通索引基本相同，区别在于：索引列的所有值都只能出现一次，即必须唯一，但可为空。
（3） 主键
是一种特殊的唯一索引，必须指定为“PRIMARY KEY”，具有唯一性的同时不能为空
（4） 全文索引
MySQL 从 3.23.23 版开始支持全文索引和全文检索。在 MySQL 中，全文索引的类型为FULLTEXT，全文索引可以在 VARCHAR 或者 TEXT 类型的列上创建。贴吧的文本内容，和一些小型的网站网页内容，存放在数据库中即为全文索引模式。
（5） 单列索引与多列索引
索引可以是单列上创建的索引，也可以是多列上创建的索引。

二、创建索引的原则依据
1、表的主键、外键必须有索引
2、数量超过 300 行的表应该有索引
3、经常与其他表进行连接的表，在连接字段上应该建立索引
4、唯一性太差的字段不适合建立索引
5、更新太频繁的字段不适合创建索引
6、经常出现在 where 字句中的字段，特别是大表的字段，应该建立索引
7、索引应该建在选择性高的字段上
8、索引应该建立在小字段上，对于大的文本字段甚至超长字段，不要建立索引

三、创建、查看、删除索引的方法
1、创建普通索引
格式：

CREATE INDEX <索引的名字> ON 表名(字段); 

示例：

mysql> create index salary_index on IT_salary(薪资);
Query OK, 0 rows affected (0.24 sec)
Records: 0	Duplicates: 0	Warnings: 0
mysql> show index from IT_salary \G;	// 或 show keys from IT_salary \G;

2、创建唯一性索引
格式：

CREATE UNIQUE INDEX <索引的名字> ON 表名(字段); 

示例：

mysql> create unique index salary_unique_index on IT_salary(姓名);
Query OK, 0 rows affected (0.06 sec) Records: 0	Duplicates: 0	Warnings: 0 mysql> show keys from IT_salary \G;

3、创建主键索引
格式：

CREATE TABLE 表名([…],PRIMARY KEY(字段)); ALTER TABLE 表名 ADD PRIMARY KEY(字段);

示例：

mysql> create table IT_salary(岗位类别 char(20) not null,姓名 char(30) not null,年 龄 int(3),
员工 ID int not null,学历 char(6),年限 int(3),薪资 char(10) not null,primary key(员工 ID));

若在新建表时忘记创建主键，可以进行如下操作：

mysql> alter table IT_salary add primary key(员工 ID);

4、查看索引
格式：

SHOW INDEX FROM 表名; SHOW KEYS FROM 表名;

示例：

mysql> show index from IT_salary \G;
mysql> show keys from IT_salary \G;

5、删除索引
格式：

DROP INDEX <索引的名称> ON 表名;
ALTER TABLE 表名 DROP INDEX <索引的名称>;
ALTER TABLE 表 名 DROP PRIMARY KEY;

注：前两条命令的作用相同，由于每个表只允许有一个 PRIMARY KEY，所以不用指定名称。示例：

mysql> drop index salary_index on IT_salary;
Query OK, 0 rows affected (0.10 sec)
Records: 0	Duplicates: 0	Warnings: 0
mysql> alter table IT_salary drop index salary_unique_index;
Query OK, 0 rows affected (0.01 sec)
Records: 0	Duplicates: 0	Warnings: 0
mysql> alter table IT_salary drop primary key;

四、事务的概念

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起想系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么不都执行。事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的
控制单元。
事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。通过事务的完整性以保证数据的一致性。

五、事务的 ACID 特点
事务具有四个属性：ACID
·原子性（Atomicity）
·一致性（Consistency）
·隔离性（Isolation）
·持久性（Durability）
1、原子性
事务是一个完整的操作，事务的各元素是不可分的（原子的），事务的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

2、一致性
当事务完成时，数据必须处于一致状态：在事务开始之前，数据库汇总存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务完成时，数据必须再次回到已知的一致状态。

3、隔离性
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应该以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

4、持久性
事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中。

六、事务的操作
默认情况下 MySQL 的事务是自动提交的，当 sql 语句提交时事务便自动提交。

1、事务处理命令控制事务
begin 开始一个事务
commit 提交一个事务
rollback 回滚一个事务（撤销）
示例：

mysql> use auth;
Database changed
mysql> begin;	//事务开始
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users values('lisi',password('123123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into users values('wangwu',password('654321'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> commit;	//事务结束并提交
Query OK, 0 rows affected (0.02 sec)

mysql> begin;	//事务开始
Query OK, 0 rows affected (0.00 sec)

mysql> update users set user_passwd=password('') where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1	Changed: 1	Warnings: 0

mysql> rollback;	//回滚（撤销操作），从 begin 开始的所有命令都将被撤销

2、使用 set 命令进行控制
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
示例：

mysql> set autocommit = 0;	//禁止自动提交
mysql> set autocommit = 1;	//开启自动提交

六、MySQL存储引擎

1、MyISAM 概述
MyISAM 存储引擎是MySQL 关系数据库系统5.5 版本之前默认的存储引擎，前身是ISAM。ISAM 是一个定义明确且经历时间考验的数据表格管理方法，在设计之时就考虑到数据
库被查询的次数要远大于更新的次数。
ISAM 的特点：ISAM 执行读取操作的速度很快,而且占用不大量的内存和存储资源，它不支持事务处理，不能够容错。
MyISAM 使用一种表格锁定的机制，以优化多个并发的读写操作。MyISAM 提供高速存储和检索，以及全文搜索能力，受到 web 开发的青睐。

2、MyISAM 的特点
（1） 不支持事务
（2） 表级锁定形式，数据在更新时锁定整个表
（3） 数据库在读写过程中相互阻塞
会在数据写入的过程中阻塞用户数据的读取 也会在数据读取的过程中阻塞用户的数据写入
（4） 可以通过 key_buffer_size 来设置缓存索引，提高访问性能，减少磁盘 IO 的压力但缓存只会缓存索引文件，不会缓存数据
（5） 采用 MyISAM 存储引擎数据单独写入或读取，速度过程较快而且占用资源相对较少。
（6） MyISAM 存储引擎不支持外键约束，只支持全文索引
（7） 每个 MyISAM 在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型。
.frm 文件存储表定义
.MYD 文件存储数据（MYData）
.MYI 文件存储索引文件（MYIndex）

3、MyISAM 使用的生成场景
（1） 公司业务不需要事务支持
（2） 一般单方读取数据比较多的业务，或单方面写入数据比较多的业务，如：www.blog，图片信息数据库，用户数据库，商品库等业务，MyISAM 存储引擎数据读写都比较频繁的场景不适合。
（3） 对数据业务一致性要求不是非常高的业务
（4） 使用读写并发访问相对较低的业务
（5） 数据修改相对较少的业务
（6） 服务器硬件资源相对比较差

1、InnoDB 的特点
（1） 支持事务：支持 4 个事务隔离级别
（2） 行级锁定，但是全表扫描仍然会是表级锁定
（3） 读写阻塞与事务隔离级别相关
（4） 具有非常高效的缓存特性：能缓存索引，也能缓存数据
（5） 表与主键以簇的方式存储
（6） 支持分区、表空间，类似 oracle 数据库
（7） 支持外键约束，5.5 以前不支持全文索引，5.5 版本以后支持全文索引
（8） 对硬件资源要求比较高
2、InnoDB 使用的生成场景
（1） 业务需要事务的支持
（2） 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引来完成
（3） 业务数据更新较为频繁的场景，如：论坛，微博
（4） 业务数据一致性要求较高，如：银行业务
（5） 硬件设备内存较大，利用 InnoDB 较好的缓存能力来提高内存利用率，减少磁盘 IO
的压力。

配置适合的存储引擎
1、查看数据库可配置的存储引擎
方法：登录 MySQL，使用 show engines;查看系统所支持的引擎
示例：

[root@mysql ~]# mysql -uroot -p123123 
mysql> show engines \G;

2、查看表正在使用的存储引擎
（1） 查看当前 MySQL 的默认引擎
show engines；
（2） 查看表使用的存储引擎
方法一：

SHOW TABLE STATUS FROM 库名 WHERE name = ‘表名’;`

mysql> show table status from client where name='user_info' \G;

方法二：

SHOW CREATE TABLE 表名;
mysql> show create table client.user_info \G;

3、配置存储引擎为所选择的类型
方法一：ALTER TABLE 表名 ENGINE=引擎;
示例：

mysql> alter table client.user_info engine=innodb;

方法二：修改 my.cnf 的 default-storage-engine 为引擎
示例：

[root@mysql ~]# vim /etc/my.cnf
[mysqld]
default-storage-engine = InnoDB

方法三：CREATE TABLE 建立表时使用 engine=引擎
示例：

mysql> use auth;
mysql> create table id(id int) engine=myisam; 

七、主从复制

原理

1. MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。
2. 要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。
3. 要打开MySQL的binlog记录功能，可通过在MySQL的配置文件my.cnf中的mysqld模块（[mysqld]标识后的参数部分）增加“log-bin”参数选项来实现。 log-bin=mysql-bin

简单描述MySQL Replication的复制原理过程

1）在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制
2）此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。
3）Master服务器接收到来自Slave服务器的I/O线程的请求后，其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
4）当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容，日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。
5）Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容，然后及时地把Relay Log文件中的内容解析成SQL语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句，并在relay-log.info中记录当前应用中继日志的文件名及位置点。

小结：

主从复制是异步的逻辑的SQL语句级的复制

复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程

实现主从复制的必要条件是主库要开启记录binlog功能

作为复制的所有MySQL节点的server-id都不能相同。

binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如select，show）语句。

主从复制简单实验
MASTER端
1、开启binlog日志功能

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin

server-id的值不可重复；修改配置文件后重启MySQL；

检查变量值

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

2、MASTER创建用于主从复制的账号
根据主从复制的原理，从库要想和主库同步，必须有一个可以连接主库的账号，并且这个账号是主库上创建的，权限是允许主库的从库连接并同步数据。

mysql> grant replication slave on *.* to 'oops'@'192.168.188.%' identified by 'wasd';
mysql> flush privileges;

解释
1.replication slave为mysql同步的必须权限，此处不要授权all权限
2.*.*表示所有库所有表，也可以指定具体的库和表进行复制。
3.‘oops’@‘192.168.188.%’ oops为同步账号。192.168.188.%为授权主机网段，使用了%表示允许整个192.168.188.0网段可以用oops这个用户访问数据库
4.identified by ‘wasd’; wasd为密码

3、对MASTER锁表只读

mysql> flush table with read lock;

锁表期间如果没有操作会自动解锁，默认自动解锁时长

4、锁表后查看主库状态。可通过当前binlog日志文件名和二进制binlog日志偏移量来查看

mysql> show master status;

命令显示的信息要记录在案，后面的从库导入全备后，继续和主库复制时就是要从这个位置开始。

5、导出数据库数据

[root@MySQL ~]# mkdir -p /server/backup
[root@MySQL ~]# mysqldump -uroot -pwasd --events -A -B | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
[root@MySQL ~]# cd /server/backup/
[root@MySQL backup]# ls
mysql_bak.2080-08-02.sql.gz
mysql> show master status;

之后再检查下 MASTER状态信息
此步骤最好新开SSH窗口。
-A表示备份所有库 -B表示增加use DB和 drop 等（导库时会直接覆盖原有的）

SLAVE端
1、设置server-id的值，关闭bin-log功能，开relay-log功能relay-log=relay-bin，完事重启服务

以下两种情况需开启bin-log功能
（1）级联同步A–>B–>C中间的B时，就要开启binlog记录功能。
（2）在从库做数据库备份，数据库备份必须要有全备和binlog日志，才是完整的备份。
2、检查参数

2、恢复数据到SLAVE

[root@QWER ~]# gzip -d mysql_bak.2020-08-02.sql.gz


mysql> source /root/mysql_bak.2080-08-02.sql

3、配置SLAVE参数

mysql> change master to master_host='192.168.188.147',master_port=3306,master_user='oops',master_password='wasd',master_log_file='mysql-bin.000016',master_log_pos=259;

MASTER_HOST= ：主库的IP
MASTER_PORT= ： 主库的端口，从库端口可以和主库不同
MASTER_USER= ： 主库上建立的用于复制的用户
MASTER_PASSWORD= ： 用户的密码
MASTER_LOG_FILE= ： show master status时查看到的二进制日志文件名称，注意不能多空格
MASTER_LOG_POS= ：show master status时查看到的二进制日志偏移量，注意不能多空格

以上信息都写在SLAVE的master.info文件中

4、开启主从复制，并检查状态

mysql> start slave;
mysql> show slave status \G;

主要步骤
1.配置my.cnf文件：主库配置log-bin和server-id参数；从库配置server-id，该值不能和主库及其他从库一样，一般不开启从库log-bin功能。注意，配置参数后要重启才能生效。

2.登陆主库，增加从库连接主库同步的账户，例如：yunjisuan，并授权replication slave同步的权限。

3.登陆主库，整库锁表flush table with read lock（窗口关闭后即失效，超时参数设置的时间到了，锁表也失效），然后show master status查看binlog的位置状态。

4.新开窗口，在Linux命令行备份导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据库数据量很大，并且允许停机，可以停机打包，而不用mysqldump。

5.导出主库数据后，执行unlock tables解锁主库。

6.把主库导出的数据恢复到从库

7.根据主库的show master status查看到的binlog的位置状态，在从库执行change master to…语句。

8.从库开启复制开关，即执行start slave；

9.从库show slave status\G,检查同步状态，并在主库进行更新测试