mysql优化-主从复制
2020-03-18 22:12:41 来源：admin 点击：736
工作原理：
在主库把数据更改记录到二进制文件中，
从库将主库的日志复制到自己的中继日志里面
从库读取中继日志事件，放到自己的库里面
操作：
主数据库
2.修改主服务器的MySQL配置文件my.ini，主要代码如下：
从数据库
server-id=2 #服务器id #要从主机同步的库
replicate-do-db=db1
replicate-do-db=db2
replicate-do-db=test
配置完重启mysql，记住！！！
3，通过show master status;查询主库状态，记录一下二进制日志文件和位置，在从服务器配置的时候要用。
3在主数据库中配置给从数据库的权限
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.20.129' IDENTIFIED BY '密码'; #主数据库授权同步账户
FLUSH PRIVILEGES; #刷新权限
SHOW MASTER STATUS; #查看主服务状态
4.在从服务器上设置同步命令
CHANGE MASTER TO MASTER_HOST='192.168.20.128',MASTER_USER='root', MASTER_PASSWORD='XXXXXX',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;
start slave; #开启SLAVE同步
show slave status \G; #查看下slave状态,如果yes表示成功
5.其他注意事项
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.20.129' IDENTIFIED BY 'XXXXXX'; #主数据库授权同步账户,哪个服务器可以登录
FLUSH PRIVILEGES; #刷新权限
SHOW MASTER STATUS; #查看主服务状态
配置从服务器的从数据库
{配置和改变slave服务器用于连接master服务器的参数【注意，MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=601是通过前面的主数据库SHOW MASTER STATUS;得到】}
当Slave_IO_Running和Slave_SQL_Running都为Yes，说明主从复制配置成功，如果有必要停止或者撤销
同步账号的权限，还可操作。
stop slave; #停止SLAVE同步
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.20.129' IDENTIFIED BY 'XXXXXX';# 撤销已经赋予给MySQL同步账户的权限
REVOKE REPLICATION SLAVE ON *.* FROM 'root'@'192.168.20.129';#revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可。
Mysql复制：master服务器将数据复制到另一台salves的过程，通过将数据库的ddl和dml的操作通过二进制传到复制服务器上，然后在从服务器重新执行。
特点：异步操作，slaves服务器不需要持续的接收数据，从服务器也可以作为其他从服务器的主服务器。
详细操作步骤：
01、主服务器将数据的改变记录到二进制日志(binary log)中；
02、从服务器将主服务器的binary log events复制到它的中继日志（relay log）中
从服务器的I/O进程会连接上master，发出日志请求，master接收到来自slave的I/O进程请求以后，通过负责复制的I/O进程通过请求信息读取指定日志位置之后的日志信息，返回给slave的I/O进程。
返回信息：日志、master服务器的文件名称以及位置，slave接收到信息，将日志内容写入relay-log的最后面，slave的SQL进程检测到新增内容后，会马上解析relay-log的内容变成可执行内容，执行。
03、从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步。
Linux下主从同步：
Mysqld_multi管理多个服务进程或者单独启动服务
Mysql5.6和mysql5.7的区别
初始化数据库操作:
Mysql5.6 mysql_install_db
Mysql5.7 /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data 初始密码
修改密码：
Set password = password(‘packphp’);
ALTER USER ‘root’@’localhost’ PASSWORD EXPIRE NEVER;
Flush privileges;
Mysqld_multi实现单机主从复制
01、开启日志：
修改etc/my.cnf
log-bin=/usr/local/mysql1/mysql-bin
server-id = 1
查看mysql服务器状态 yum install net-tools
02、登录主服务器，设置一个复制用户的权限
Grant replication slave
03、在master服务，设定度锁定有限
Flush tables with read lock;
04、查看日志状态
Show master status 二进制名称和偏移量值
Unlock tables;
Show slaves status; 查看从服务器的状态
Show processlist\G
不同服务器之间的主从同步：
要求：mysql版本保持一致
从服务器数据库做设置：
指定复制使用的用户、主数据库服务器的IP、端口、以及开始执行复制的日志文件和位置
Mysqld_safe --skip-slave-start &
Stop slave
Change master to
Master_host=’192.168.0.101’,
Master_user=’rep1’,
Master_password=’123456’,
Master_log_file=’mysql-bin.00001’,
Master_log_pos=100;
Start slave;
阿里和腾讯云就不建议做主从同步，服务器之间本来就有延迟。同一局域网可以做
主要复制启动选项：
从服务器的启动参数、log-slave-updates(从服务器作为其它从服务器的主服务器-链式的复制)、 master-connect-retry 主从服务器丢失连接重新连接的时间间隔 （60秒）、read-only、slave-skip-errors(跳过binlog的错误的SQL语句)
指定复制的数据库或者数据表
Replicate-do-table=dbname.tablename
主从数据库管理、维护
查看slave的复制进度
Show processlist slave_sql_running SQL时间和系统时间的差距
Show slave status\G;
常见错误处理：
表结构不同
blob或者长字符串 max_allowed_packet=16777216
多主复制的自增长变量冲突问题
主从服务器的切换：
01、确保所有的relay_log全部更新
Show processlist\G; Has read all relay log
02、停止B的slave服务，然后执行reset master重置成主数据库
Stop slave; reset master;
03、配置B服务器的账号，在C服务器运行change master to ..........