一:MYSQL主从配置
1.1 部署环境
主(master_mysql): 192.168.0.121 OS:CentOS 7
从(slave_mysql): 192.168.0.122 OS:CentOS 7
1.2 安装mysql
主和从: yum install mysql-server
1.3 配置
1.3.1 主配置(master_mysql配置
vim /etc/my.conf
server-id=121 #设置主服务器的ID
innodb_flush_log_at_trx_commit=2 #
sync_binlog=1 #开启binlog日志同步功能
log-bin=mysql-bin-121#binlog日志文件名
binlog-do-db=xxxx # 这个表示只同步某个库 (如果没有此项,表示同步所有的库)
1.3.2 配置完后,重启主库的mysql
service mysqld restart
如果开启远程可以略过这步
mysql -uroot -p ##登录mysql
mysql>grant replication slave on *.* to 'mark'@'192.168.1.201' identified by '123456'; ##授权给从数据库服务器192.168.1.201,用户名mark,密码123456
mysql>show master status ; ##查看主库的状态 file,position这两个值很有用。要放到slave配置中
mysql> show master status ;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-121.000001 | 155 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1.3.3 配置从库服务器
vim /etc/my.cnf
server-id=122
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=mysql-bin-122
1.3.4 配置完后,重启从库的mysql
service mysqld restart
mysql -uroot -p
mysql>stop slave;
mysql> change master to master_host='192.168.0.121',master_user='root',master_password='123456',master_log_file='mysql-bin-121.000001',master_log_pos=155;
mysql> start slave; ##开启从库 (stop slave:关闭从库)
mysql> show slave status \G; ###Slave_IO_Running,Slave_SQL_Running 都为Yes的时候表示配置成功
1.3.5master数据库 设置只读操作
mysql> FLUSH TABLES WITH READ LOCK;