mysql高级
mysql
用于数据库连接管理
将用户sql语句发送到服务器
mysqladmin
命令行管理工具
mysqldump
备份数据库和表的内容
mysqldumpslow
慢日志分析
字符串问题
事务
日志
备份与恢复(mysqldump\xtrdumpbackup)
高可用
读写分离
字符集问题
show charset;字符集 show collation；校对规则 ci(大小写不敏感） cs/bin（大小写敏感）
------------------------------ cmake . -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \
--------------------------------
[mysqld] character-set-server=utf8
[client]
default-character-set=utf8
---------------------------------
CREATE TABLE `test` (
`id` int(4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysqlshow工具
show语句
• SHOW databases：列出所有数据库 • SHOW TABLES：列出默认数据库中的表 • SHOW TABLES FROM <database_name>：列出指定数据库中的表 • SHOW COLUMNS FROM <table_name>：显示表的列结构 • SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息 • SHOW CHARACTER SET：显示可用的字符集及其默认整理 • SHOW COLLATION：显示每个字符集的整理 • SHOW STATUS：列出当前数据库状态 • SHOW VARIABLES：列出数据库中的参数定义值
describe语句
等效于 show columns
mysqlshow -u<user_name> -p<password> [db_name [table_name[column_name]]]
与show语句相似
show profile
提供用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量
1、是否支持
show varibles like 'profiling'
2、开启
set profiling=on;
3、运行sql
4、查询结果
show profiles; 会返回查询语句的统计信息。
5、诊断sql
show profiles cpu,block io for query 3
索引
普通 index 唯一 unique index 联合 index(a,b) 前缀 index(a(3));
alter table t1 add index index_name(field_name); create index index_name on t1(field_name); alter table t1 drop index index_name desc t1; show index from t1\G explain select SQL_NO_CACHE * from test where name='oldboy'\G SQL_NO_CACHE的作用是禁止缓存查询结果。
ALL,index, range, ref, eq_ref, const, system, NULL 从左到右，性能从最差到最好
ALL 全表扫描
index 只遍历索引树
range 索引范围扫描
ref 使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行 where stu_name='xiaoming'
eq_ref 类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一行记录
const\system 当sql对查询某部分进行优化，并转化为一个常量时，使用这些类型访问，如将主键置于where列表中
NULL sql在优化过程中分解语句，执行时甚至不用访问表或索引。例如从一个索引列里选取最小值可以通过单独索引查找完成
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。 那么索引设计原则又是怎样的? 1．选择唯一性索引 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。 例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。 如果使用姓名的话，可能存在同名现象，从而降低查询速度。 主键索引和唯一键索引，在查询中使用是效率最高的。 2．为经常需要排序、分组和联合操作的字段建立索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。 如果为其建立索引，可以有效地避免排序操作。 3．为常作为查询条件的字段建立索引 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此， 为这样的字段建立索引，可以提高整个表的查询速度。 select count(DISTINCT population ) from city; select count(*) from city; 4．尽量使用前缀来索引 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索 会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 ------------------------以上的是重点关注的，以下是能保证则保证的-------------------- 5．限制索引的数目 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 6．尽量使用数据量少的索引 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文 检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。 7．删除不再使用或者很少使用的索引 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理 员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
不走索引的情况： 重点关注： 1) 没有查询条件，或者查询条件没有建立索引 select * from tab; 全表扫描。 select * from tab where 1=1; 在业务数据库中，特别是数据量比较大的表。 是没有全表扫描这种需求。 1、对用户查看是非常痛苦的。 2、对服务器来讲毁灭性的。 （1）select * from tab; SQL改写成以下语句： selec * from tab order by price limit 10 需要在price列上建立索引 （2） select * from tab where name='zhangsan' name列没有索引 改： 1、换成有索引的列作为查询条件 2、将name列建立索引 2) 查询结果集是原表中的大部分数据，应该是30％以上。 查询的结果集，超过了总数行数30%，优化器觉得就没有必要走索引了。 假如：tab表 id，name id:1-100w ，id列有索引 select * from tab where id>500000; 如果业务允许，可以使用limit控制。 怎么改写 ？ 结合业务判断，有没有更好的方式。如果没有更好的改写方案 尽量不要在mysql存放这个数据了。放到redis里面。 3) 索引本身失效，统计数据不真实 索引有自我维护的能力。 对于表内容变化比较频繁的情况下，有可能会出现索引失效。 4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 例子： 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给数据库, 这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn='13333333333'; ------------------------ mysql> alter table tab add index inx_tel(telnum); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> mysql> desc tab; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | telnum | varchar(20) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select * from tab where telnum='1333333'; +------+------+---------+ | id | name | telnum | +------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> select * from tab where telnum=1333333; +------+------+---------+ | id | name | telnum | +------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum='1333333'; +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum=1333333; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum=1555555; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum='1555555'; +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> ------------------------ 6) <> ，not in 不走索引 EXPLAIN SELECT * FROM teltab WHERE telnum <> '110'; EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119'); ------------ mysql> select * from tab where telnum <> '1555555'; +------+------+---------+ | id | name | telnum | +------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum <> '1555555'; ----- 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit or或in 尽量改成union EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119'); 改写成： EXPLAIN SELECT * FROM teltab WHERE telnum='110' UNION ALL SELECT * FROM teltab WHERE telnum='119' ----------------------------------- 7) like "%_" 百分号在最前面不走 EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描 EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引 %linux%类的搜索需求，可以使用elasticsearch %linux培训% 8) 单独引用复合索引里非第一位置的索引列. 列子： 复合索引： DROP TABLE t1 CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT); ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex); DESC t1 SHOW INDEX FROM t1 走索引的情况测试： EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m'; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 ; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m'; ----->部分走索引 不走索引的： EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20 EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m'; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m'; ------------------------------------------------
存储引擎
1、使用 SELECT 确认会话存储引擎： SELECT @@default_storage_engine; 2、使用 SHOW 确认每个表的存储引擎： SHOW CREATE TABLE City\G SHOW TABLE STATUS LIKE 'CountryLanguage'\G 3、使用 INFORMATION_SCHEMA 确认每个表的存储引擎： SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'City' AND TABLE_SCHEMA = 'world_innodb'\G
1、在启动配置文件中设置服务器存储引擎： [mysqld] default-storage-engine=<Storage Engine> 2、使用 SET 命令为当前客户机会话设置： SET @@storage_engine=<Storage Engine>; 3、在 CREATE TABLE 语句指定： CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
系统表空间
Innodb共享表空间设置： [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend ----错误的配置XXX innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend -----正确的配置
alter table t1 discard tablespace; 卸载表空间
alter table t1 import ***.ibd导入
事务ACID
原子性 Atomic
一致性 Consistent
隔离性 Isolated
持久性 Durable
START TRANSACTION（或 BEGIN）：显式开始一个新事务
SAVEPOINT：分配事务过程中的一个位置，以供将来引用
COMMIT：永久记录当前事务所做的更改
ROLLBACK：取消当前事务所做的更改
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT：删除 savepoint 标识符
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式
在MySQL5.5开始，开启事务时不再需要begin或者start transaction语句。并且，默认是开启了Autocommit模式，作为一个事务隐式提交每个语句。 在有些业务繁忙企业场景下，这种配置可能会对性能产生很大影响，但对于安全性上有很大提高。 将来，我们需要去权衡我们的业务需求去调整是否自动提交。 我们可以通过以下命令进行修改关闭（0是关闭，1是开启）： SET GLOBAL AUTOCOMMIT=0; - 所有新建会话
SET SESSION AUTOCOMMIT=0; - 当前会话 SELECT @@AUTOCOMMIT; - 查看设置结果 我们也可以修改配置文件让其永久生效： vi /etc/my.cnf [mysqld] AUTOCOMMIT=0
事务日志redo
在事务ACID过程中，实现的是“D”持久化的作用。
事务日志undo
在事务ACID过程中，实现的是“A、C”原子性和一致性的作用。
日志管理
错误日志
配置方法: [mysqld] log-error=/data/mysql/mysql.log 查看配置方式： mysql> show variables like '%log%error%'; 作用： 记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。
查询日志
配置方法: [mysqld] general_log=on general_log_file=/data/mysql/server2.log 查看配置方式： show variables like '%gen%'; 作用： 记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启
二进制文件
将已提交的数据记录，以event的形式记录到二进制文件中。用于备份恢复，复制
格式：
row 行模式，（记录事件）推荐
statement， 语句模式
mixed （以上两者混合）
1、开启二进制日志 set sql_log_bin=0 在会话级别修改为临时关闭 vi /etc/my.cnf log-bin=/data/mysql/mysql-bin 在全局打开binlog binlog-format=ROW mysql> SET GLOBAL binlog_format = 'STATEMENT'; mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED'; 3、查看binlog设置 show variables like '%binlog%';
查询二进制日志文件 ls -l /data/mysql/mysql-bin* mysql> SHOW BINARY LOGS; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000015 | 724935 | | binlog.000016 | 733481 | mysql> SHOW MASTER STATUS; +---------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | binlog.000016 | 733481 | world_innodb | manual,mysql |
flush logs 刷新二进制日志
mysqlbinlog "D:\Program Files\MariaDB 10.3\data.000001" 获取日志信息
SHOW BINLOG EVENTS in 'data.000001'
截取二进制日志
mysqlbinlog --start-position=4 --stop-position=256 >123.sql
删除二进制日志
默认情况下，不会删除旧的日志文件。
根据存在时间删除日志：
SET GLOBAL expire_logs_days = 7;
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
慢日志
long_query_time=3 ： 设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s slow_query_log=1 ： 指定是否开启慢查询日志 slow_query_log_file ： 指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log min_examined_row_limit ：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 log_queries_not_using_indexes : 不使用索引的慢查询日志是否记录到索引
show global status like '%Slow_queries%' # 查询有几条慢日志
分析工具 自带
mysqldumpslow -s c -t 10 /database/mysql/slow-log
这会输出记录次数最多的10条SQL语句，其中： -s 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙； -t 是top n的意思，即为返回前面多少条的数据；
-c 次数
-l 锁定时间
备份与恢复
备份的类型
热备份
冷备份
温备份
备份的方式
逻辑备份 （sql语句） 物理备份 （数据文件的二进制副本）
增量备份 （刷新二进制日志）
基于复制的备份）
备份工具
mysqldump
mysql原生自带的逻辑备份工具
mysqlbinlog
实现binlog备份的原生态命令
xtrabackup
precona公司开发的高性能的物理备份工具
mysqldump
-A, --all-databases 全库备份 -B 增加建库（create）及“use库”的语句，可以直接接多个库名，同时 备份多个库***** -B 库1 库2；不加-B，恢复时要先建库，+use, -R, --routines 备份存储过程和函数数据 --triggers 备份触发器数据
-F,--flush-logs 刷新binlog日志
--master-data={1|2} 告诉你备份之后时刻的binlog的位置 （2，注释；1，非注释，要执行（主从复制））
-x,--lock-all-tables
-l,--lock-tables
--single-transaction 对innodb引擎进行预热；(通过快照的方式) 设置事务的隔离级别为可重复读，即REPEATABLE READ，
这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，
对该dump线程的数据并无影响，在这期间不会锁表。
mysqldump 库1 表1 表2 表3 >库1.sql
mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip >/opt/all_$(date +%F).sql.gz
xtrabackup
物理备份工具 备份方式： 1、拷贝数据文件 2、拷贝数据页 备份原理： 1、对于innodb表，可以实现预热
- 在数据还有修改操作时，直接将数据文件中的数据页备份，此时，备份走的数据对于当前mysql来讲不一致
- 将备份过程中的redo\undo一并备走
- 为了恢复的时候，只要保证分贝出来的数据页LSN能和redo LSN匹配，将来恢复的就是一致的数据。
- 对于myisam表，实现自动锁表拷贝文件
1、安装 wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm 备份命令: xtrabackup innobackupex ******
全备份
innobackupex --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp /server/backup/full
--no-timestamp 默认会生成一个日期文件夹，在里面备份
恢复数据前的准备(合并xtabackup_log_file和备份的物理文件) 1、 innobackupex --apply-log --use-memory=32M /server/backup/full/
2、 关闭mysqld
3、 cp -a /server/backup/full /application/mysql/data
4、 chown -R mysql.mysql /application/mysql/data
5、 启动mysqld
1、全备： innobackupex --user=root --password=123 --no-timestamp /backup/full/ 2、模拟数据变化 3、第一增量： innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/full/ /backup/inc1 4、模拟数据变化 5、第二次增量： innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/inc1 /backup/inc2 6、模拟数据损坏 7、恢复数据： innobackupex --apply-log --redo-only /backup/full innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full innobackupex --apply-log /backup/full
高可用
辅助备份
高可用
分担负载
主从复制
主服务器将所有数据结构和更改记录到二进制日志中
从属服务器从主服务器请求该二进制日志并在本地应用其内容
涉及的线程
主库：
dump thread
从库:
io thread
sql thread
涉及的日志及文件
主库
binlog
从库
relay-log
relay-index
master.info
relay-log.info
思路
1、 两个节点
2、 主库开启binlog，从库开启relay-log
3、 server-id 不同
4、 主库创建复制账户
5、 主库备份并记录二进制文件和position
6、 从库 change master to 连接主库
7、 启动从库复制
8、 验证主从 show slave status\G
MySQL Replication ------------------------ 主从复制原理： 1、主从复制的前提 1.1 两台以上mysql实例 多台物理机 多个mysql实例 1.2 主库要开启二进制日志 1.3 主库要提供复制相关的用户 replication slave，一个比较特殊的权限 grant replication slave on *.* to repl@'10.0.0.%' identified by '123'; 1.4 从库需要将和主库相差的数据，进行追加 一般情况下可以人为备份主库数据，恢复到从库上 1.5 从应该从恢复之后的时间点，开始自动从主库获取新的二进制日志开始应用 我们需要人为告诉从库，从哪开始自动开始复制二进制日志(file+position)，另外还需要告诉从库user,passwd,port,ip change master to ------------------- 2、复制中的线程及文件 2.1、主库 Dump（IO） thread：在复制过程中，主库发送二进制日志的线程 2.2、从库 IO thread：向主库请求二进制日志，并且接受二进制日志的线程 SQL thread：执行请求过来的二进制的线程 2.3、主库 binlog文件：主库的二进制日志 2.4、从库 relaylog:中继日志，存储请求过来的二进制日志 master.info: 1、从库连接主库的重要参数（user,passwd,ip,port） 2、上次获取过的主库二进制日志的位置 relay-log.info 存储从库SQL线程已经执行过的relaylog日志位置 ------------------ 3、主从复制的工作原理 3.1 从库，IO线程，读取master.info中的信息， 获取到连接参数（user\passwd\ip\port）+上次请求过的主库的binlog的位置(例子：mysql-bin.000003，position=640) 3.2 IO线程使用链接到主库，拿着位置信息（mysql-bin.000003，position=640），问主库有没有比这个更新的二进制日志。 3.3 主库查询二进制日志，并对比从库发送过来的位置信息（mysql-bin.000003，position=640），如果有新的二进制日志，会通过 dump thread发送给从库。 3.4 从库通过IO线程，接受主库发来的二进制日志，存储到TCP/IP缓存中，并且返回“ACK”确认给主库，这时主库收到ACK后， 就认为复制完成了，可以继续其他工作了。 3.5 从库更新master.info，二进制日志位置更新为新的位置信息。 3.6 从库IO线程会将TCP/IP缓存中的日志，存储到relay-log中继日志文件中。 3.7 从库SQL线程，读取relay-log.info,获取到上次执行到的relaylog日志位置，以这个位置信息作为起点，往后继续执行中继日志。 3.8 SQL线程执行完成所有relay之后，会更新relay-log.info信息为新位置信息。 到此为止，一次完整的复制过程就完成了。 ----------------------------- 主从复制搭建实战： 1、准备环境 思路： 1、两个以上节点（多实例） 3307：master 3308：slave1 3309：slave2 2、主库binlog开启，从库开启relay-log（默认在数据目录下生成） vim /data/3307/my.cnf log-bin=/data/3307/mysql-bin binlog_format=row 3、server-id不同 [root@db02 data]# cat /data/3307/my.cnf |grep server-id server-id=3307 [root@db02 data]# cat /data/3308/my.cnf |grep server-id server-id=3308 [root@db02 data]# cat /data/3309/my.cnf |grep server-id server-id=3309 4、关闭数据库的自动域名解析 每个节点都加入以下配置： skip-name-resolve 5、启动多实例 mysqld_safe --defaults-file=/data/3307/my.cnf & mysqld_safe --defaults-file=/data/3308/my.cnf & mysqld_safe --defaults-file=/data/3309/my.cnf & 6、主库创建复制账户 连接到主库： mysql -S /data/3307/mysql.sock grant replication slave on *.* to repl@'10.0.0.%' identified by '123'; 7、从库数据的追加 （1）不需要追加的情况 主和从同时搭建的新环境，就不需要备份主库数据，恢复到从库了，直接从第一个binlog（mysql-bin.000001）的开头位置（120） （2）如果主库已经工作了很长时间了，我们一般需要备份主库数据，恢复到从库，然后从库从备份的时间点起自动进行复制 重点针对第二种情况进行演示： 备份主库： mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql sed -n '22p' /tmp/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=325 恢复到从库： mysql -S /data/3308/mysql.sock mysql> set sql_log_bin=0; mysql> source /tmp/full.sql 8、从库开启主库： mysql -S /data/3308/mysql.sock help change master to CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=325; 开启主从（开启IO和SQL线程）： start slave; 9、查看主从状态： show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes -------------- 10、主从重要状态信息介绍 show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: IO线程故障： 1、主库连接不上 user、password、port、ip 错误 解决方案： stop slave; reset slave all; change master to start slave; 防火墙 网络不通 skip-name-resolve stop slave; start slave; 2、主库二进制日志丢失或损坏 解决方案： stop slave; reset slave all; 重新备份恢复 change master to start slave; SQL线程故障： 执行relaylog日志新事件 1、删除、修改对象的操作时，没有这个对象 2、创建对象时，对象已存在 3、主键冲突 从库做写入操作，会导致以上问题出现 处理方法： stop slave; set global sql_slave_skip_counter = 1; start slave; /etc/my.cnf slave-skip-errors = 1032,1062,1007 但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。 怎么预防以上问题？ 从库加入配置文件 set global read_only=1; vim /etc/my.cnf read_only=1 ---->只能控制普通用户 ------------------ 11、主从异常——主从延时过长 show slave status \G Seconds_Behind_Master:0 默认的主从复制机制是异步的一个过程。 主库原因: 1、主库做修改操作之后，才会记录二进制日志。 sync_binlog=0/1 If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast) --------------------- 1：表示：每次事务commit，刷新binlog到磁盘 0：系统决定binlog什时候刷新到磁盘 2、主库的压力特别大（大事务、多事务） 3、从库数量多，导致dump线程繁忙 ------------------- 从库原因： 1、relay-log写入慢 2、SQL线程慢（主从硬件差异比较大） ----------------------------- 尽可能的避免主从延时 1、sync_binlog=1 2、大事务拆成小事务，多事务进行分离 3、使用多级主从，分库分表架构 4、将binlog放到ssd或者flash上，高性能存储 5、将relay放到ssd或者flash上 6、尽量选择和主库一致硬件和配置 ------------------ 12、主从复制高级功能——半同步复制 出发点：保证主从数据一致性的问题，安全的考虑 5.5 出现的概念，但是不建议使用，性能太差 5.6以后出现group commit 组提交功能，来提升开启版同步复制的性能 5.7 增强半同步复制的新特性：after sync; ------ 加载插件 主: INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 从: INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 查看是否加载成功: show plugins; 启动: 主: SET GLOBAL rpl_semi_sync_master_enabled = 1; 从: SET GLOBAL rpl_semi_sync_slave_enabled = 1; 重启从库上的IO线程 STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; 查看是否在运行 主: show status like 'Rpl_semi_sync_master_status'; 从: show status like 'Rpl_semi_sync_slave_status'; ----- 补充： rpl_semi_sync_master_timeout | 10000 默认情况先,到达10秒钟还没有ack，主从关系自动切换为普通复制 如果是1主多从的半同步复制，只要有一台落地relaylog，返回ack，这次半同步就完成了。 -------------------------------------- 13、主从复制高级功能——延时从库 会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份 我们配置的是SQL_thread的延时 mysql>stop slave; mysql>CHANGE MASTER TO MASTER_DELAY = 60; mysql>start slave; mysql> show slave status \G SQL_Delay: 300 取消延时： mysql> stop slave; mysql> CHANGE MASTER TO MASTER_DELAY = 0; mysql> start slave; ----------------------- 14、主从复制高级功能——复制过滤 主库方面控制（不建议使用）： 白名单:只记录白名单中列出的库的二进制日志 binlog-do-db 黑名单：不记录黑名单列出的库的二进制日志 binlog-ignore-db 从库方面控制： 白名单：只执行白名单中列出的库或者表的中继日志 --replicate-do-db=test --replicate-do-table=test.t1 --replicate-wild-do-table=test.x* 黑名单：不执行黑名单中列出的库或者表的中继日志 --replicate-ignore-db --replicate-ignore-table --replicate-wild-ignore-table 只复制world数据库的数据 -------------- 15、主从复制新特性——GTID复制 GTID 5.6新特性 GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。 它的官方定义如下： GTID = source_id ：transaction_id 7E11FA47-31CA-19E1-9E56-C43AA21293967:29 每一台mysql实例中，都会有一个唯一的uuid，标识实例的唯一性 auto.cnf，存放在数据目录下 重要参数： gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 gtid-mode=on --启用gtid类型，否则就是普通的复制架构 enforce-gtid-consistency=true --强制GTID的一致性 log-slave-updates=1 --slave更新是否记入日志 ----------------- 构建1主2从的GTID复制环境： 3台虚拟机， db02 克隆两台虚拟机环境，分别命名为db01、db03，在生产中准备3台真实的物理机，不用多实例 要求： 1、IP地址、主机名 db01:10.0.0.51/24 db03:10.0.0.53/24 2、清理所有之前3306的相关数据，只留软件 db01: cd /application/mysql/data/ \rm -rf * cd /data/binlog/ \rm -rf * db02: cd /application/mysql/data/ \rm -rf * cd /data/binlog/ \rm -rf * db03: cd /application/mysql/data/ \rm -rf * cd /data/binlog/ \rm -rf * 3、准备配置文件 规划： 主库： 10.0.0.51/24 从库1: 10.0.0.52/24 从库2：10.0.0.53/24 主库： 加入以下配置信息 db01:10.0.0.51/24 vim /etc/my.cnf [mysqld] basedir=/application/mysql datadir=/application/mysql/data socket=/tmp/mysql.sock log-error=/var/log/mysql.log log_bin=/data/binlog/mysql-bin binlog_format=row skip-name-resolve server-id=51 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [client] socket=/tmp/mysql.sock slave1： db02:10.0.0.52/24 vim /etc/my.cnf [mysqld] basedir=/application/mysql datadir=/application/mysql/data socket=/tmp/mysql.sock log-error=/var/log/mysql.log log_bin=/data/binlog/mysql-bin binlog_format=row skip-name-resolve server-id=52 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [client] socket=/tmp/mysql.sock slave2： db02:10.0.0.53/24 vim /etc/my.cnf [mysqld] basedir=/application/mysql datadir=/application/mysql/data socket=/tmp/mysql.sock log-error=/var/log/mysql.log log_bin=/data/binlog/mysql-bin binlog_format=row skip-name-resolve server-id=53 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [client] socket=/tmp/mysql.sock ----------------- 三台节点分别初始化数据： /application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/ 分别启动三个节点mysql： /etc/init.d/mysqld start 测试启动情况： mysql -e "show variables like 'server_id'" master:51 slave:52,53 51: grant replication slave on *.* to repl@'10.0.0.%' identified by '123'; 52\53: change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1; start slave; -------------
浙公网安备 33010602011771号