mysql强制读主库命令_mysql5.7 yum安装及主从配置（从库只读），不重启主库添加从库配置...

yum -y remove mysql

wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

rpm -ivh mysql57-community-release-el7-8.noarch.rpm

yum -y install mysql-server

systemctl start mysqld

主从都要做：

mysql5.7.x版本后默认yum安装会生成一个初始密码

cat /var/log/mysqld.log |grep password

查看初始密码登录数据库后修改密码

ALTER USER USER() IDENTIFIED BY '新密码';

flush privileges;

主库操作：

修改主库配置文件

/etc/my.conf

[mysqld]下添加

init_connect='SET collation_connection = utf8mb4_unicode_ci'

init_connect='SET NAMES utf8mb4'

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

skip-character-set-client-handshake

server-id=2

log-bin=log

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

expire_logs_days = 30 ##binlog日志过期时间，默认不过期

max_binlog_size = 100M ##binlog日志达到该值后切割，默认1G

[client]

default-character-set=utf8mb4

[mysql]

default-character-set=utf8mb4

保存重启数据库

systemctl restart mysql

进数据库创建同步账号

create user 'sync'@'%' identified by 'Sync!0000';

grant FILe on *.* to 'sync'@'192.168.0.145' identified by 'Sync!0000';

grant replication slave on *.* to 'sync'@'192.168.0.145' identified by 'Sync!0000';

flush privileges;

show master status; 记录File和Position对应的信息，我这里是log.000006和151

从库操作：

修改从库配置文件

[mysqld]下添加

init_connect='SET collation_connection = utf8mb4_unicode_ci'

init_connect='SET NAMES utf8mb4'

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

skip-character-set-client-handshake

server-id=3

log-bin=mysql-bin

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-ignore-db=sys

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

read_only=1

expire_logs_days = 30 ##binlog日志过期时间，默认不过期

max_binlog_size = 100M ##binlog日志达到该值后切割，默认1G

[client]

default-character-set=utf8mb4

[mysql]

default-character-set=utf8mb4

保存重启数据库

systemctl restart mysql

进入数据库

stop slave;

change master to master_host='192.168.0.135',master_user='sync',master_password='Sync!0000',master_log_file='log.000003',master_log_pos=154; (最关键的一步，将主库ip，同步账号的用户密码，show master status得到的file、position写入。在5.7之前的版本这一步的相关参数是写到配置文件中的，5.7如果再写进配置文件会报参数错误)

start slave;

show slave status \G;

read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户(如超级管理员root用户)的数据修改操作。

如果想保证super用户也不能写操作，就可以就需要执行给所有的表加读锁的命令 “flush tables with read lock;”。(但是退出mysql进程后 该配置就失效了 root账号继续可以写入，一般迁移时用到)

GRANT select,insert,update,delete ON *.* TO '普通账号'@'%' IDENTIFIED BY '密码' ; 给从库创建一个普通账号有增删改查的权限

用这个账号连接从库进行添加数据或者删除数据或者update数据都会失败。

必须用 指定部分权限的普通用户才行， 不能给ALL权限，否则就不能做到只读。

还有不能在末尾加WITH GRANT OPTION，如果加了 可能可以自己给自己加ALL权限。以防万一不加

至此主库读写，从库同步+只读完成

(若root账号需要远程连接则GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'youpassword' ;

FLUSH PRIVILEGES; 允许任何IP访问)

不重启主库的情况下新增从库

xtrabackup需要yum安装该软件

xtrabackup是物理备份，备份速度快，不锁表。为什么不锁表？因为自身会监控主库日志，如果有更新的数据，就会先写到一个文件中，然后再回归到备份文件中，从而保持数据一致性。

centos7下

先主库安装xtra的仓库

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

再装2.4.12的最新版本包

yum install percona-xtrabackup-24

(mysql5.7以上需要2.4以上的xtra，如有更新下载最新的xtra)

新建一个专用备份的目录

mkdir /mysqlbackup

cd /mysqlbackup

备份到该目录

innobackupex --user=root --password=密码 ./

拷贝目录所有文件到新增的从库服务器

scp -r 2018-11-27_08-20-19/ root@192.168.0.141:/root/

从库操作

先按照上文修改从库默认密码，再改从库配置文件注意server-id要不同

再停服务，删datadir(别跑路)，把备份改为datadir，赋权限，重启服务

systemctl stop mysqld

rm -rf /var/lib/mysql

mv 2018-11-27_08-20-19/ /var/lib/mysql

chown mysql.mysql -R /var/lib/mysql

systemctl start mysqld

这回用主库账号进去查看(如果主库有%远程访问权限，记得改掉，一会重开只读账号)，发现主库的库都在 就是成功了。

再去主库创建一张新表随便添点数据(模拟备份后新增数据)

从备份目录中xtrabackup_info文件获取到binlog和pos位置

cat /var/lib/mysql/xtrabackup_info

进入数据库同步主库 填入xtrabackup_info的binlog文件名和pos值

stop slave;

change master to master_host='192.168.0.135',master_user='sync',master_password='Sync!0000',master_log_file='log.000003',master_log_pos=1872;

启动同步前需要在主库上允许这个新从库IP访问

grant FILe on *.* to 'sync'@'192.168.0.141' identified by 'Sync!0000';

grant replication slave on *.* to 'sync'@'192.168.0.141' identified by 'Sync!0000';

flush privileges;

再到从库开启同步

start slave;

查看是否成功

show slave status\G;

成功后新增从库已经把备份后产生的表都同步过来了

但是还没完，至此从库的账号权限和主库是一样的(虽然从库同步排除了mysql库，但是xtra是物理备份了主库所有文件)，如果从库要做成只读就必须要新开个只读账号。参照上文