mysql设置主从数据库

参考文章

mysql 主从配置，slave master 设置

MySQL数据同步，出现Slave_SQL_Running：no和slave_io_running：no问题的解决方法

MySQL [Warning]: IP address ‘xxxx’ could not be resolved: Name or service not known

MHA集群报错：The slave I/O thread stops because master and slave have equal MySQL server UUIDs

1， master的mysql配置

master有数据库 ds0 , ds1

找到 mysql的my.cnf文件：

 mysql –help | grep my.cnf 

运行结果

修改 my.cnf文件

vim /etc/my.cnf

添加以下配置：

log-bin=mysql-bin #添加这一行就ok 
binlog-format=ROW #选择row模式 
server_id=1 #配置mysql replaction需要定义，不能和canal的slaveId重复
expire_logs_days = 10 #日志保存时间
binlog-ignore-db=information_schema #这是mysql默认的数据库,忽略掉
binlog-ignore-db=mysql              #这是mysql默认的数据库,忽略掉
binlog-ignore-db=performance_schema #这是mysql默认的数据库,忽略掉
binlog-do-db=ds0 #需要同步的数据库
binlog-do-db=ds1 #需要同步的数据库

登录master的数据库，创建同步的账号

mysql -u 用户名 -p

#创建用户slaveuser 密码123456
CREATE USER 'slaveuser'@'%' IDENTIFIED BY '123456';
 
#给读取文件权限
grant FILE on *.* to 'slaveuser'@'192.168.16.129' identified by '123456'; 
grant replication slave on *.* to 'slaveuser'@192.168.16.129' identified by '123456';
 
flush privileges;

重启mysql server

service mysqld restart

重新登录master的数据库：

show master status; 

如上设置成功

2，slave的mysql配置

创建 ds0 , ds1 的备份master数据库 ds0 , ds1

create database ds0 default charset utf8mb4;
create database ds1 default charset utf8mb4;

把master数据库的数据dump一份导入到slave(非常重要)，备份master的sql文件：

mysqldump  -h 192.168.16.128  -u root  -p  ds0 > source.sql  #dump master数据库到slave

说明： mysqldump 备份命令，语法:

mysqldump -u <username> -p <dbname> > /path/to/***.sql

参数解析
 1 -A --all-databases：导出全部数据库
 2 -Y --all-tablespaces：导出全部表空间
 3 -y --no-tablespaces：不导出任何表空间信息
 4 --add-drop-database每个数据库创建之前添加drop数据库语句。
 5 --add-drop-table每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
 6 --add-locks在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
 7 --comments附加注释信息。默认为打开，可以用--skip-comments取消
 8 --compact导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
 9 -c --complete-insert：使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
10 -C --compress：在客户端和服务器之间启用压缩传递所有信息
11 -B--databases：导出几个数据库。参数后面所有名字参量都被看作数据库名。
12 --debug输出debug信息，用于调试。默认值为：d:t:o,/tmp/
13 --debug-info输出调试信息并退出
14 --default-character-set设置默认字符集，默认值为utf8
15 --delayed-insert采用延时插入方式（INSERT DELAYED）导出数据
16 -E--events：导出事件。
17 --master-data：在备份文件中写入备份时的binlog文件，在恢复进，增量数据从这个文件之后的日志开始恢复。值为1时，binlog文件名和位置没有注释，为2时，则在备份文件中将binlog的文件名和位置进行注释
18 --flush-logs开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
19 --flush-privileges在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
20 --force在导出过程中忽略出现的SQL错误。
21 -h --host：需要导出的主机信息
22 --ignore-table不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
23 -x --lock-all-tables：提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
24 -l --lock-tables：开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
25 --single-transaction：适合innodb事务数据库的备份。保证备份的一致性，原理是设定本次会话的隔离级别为Repeatable read，来保证本次会话（也就是dump）时，不会看到其它会话已经提交了的数据。
26 -F：刷新binlog，如果binlog打开了，-F参数会在备份时自动刷新binlog进行切换。
27 -n --no-create-db：只导出数据，而不添加CREATE DATABASE 语句。
28 -t --no-create-info：只导出数据，而不添加CREATE TABLE 语句。
29 -d --no-data：不导出任何数据，只导出数据库表结构。
30 -p --password：连接数据库密码
31 -P --port：连接数据库端口号
32 -u --user：指定连接的用户名。

执行ls命令，可以看到生成的source.sql文件

把master的数据备份到slave中

mysql  -h 192.168.16.129  -u root  -p  ds0 < source.sql #导入数据到slave的testOrderDb数据库中

修改my.cnf配置文件

vim /etc/my.cnf

添加以下配置：

log-bin=mysql-bin
binlog_format=ROW 
server-id   = 2  #注意:master机器设置了1,这里设置跟master不同的server id
expire_logs_days = 10
binlog-ignore-db=information_schema #忽略默认的information_schema数据库
binlog-ignore-db=mysql              #忽略默认的mysql数据库
binlog-ignore-db=performance_schema #忽略默认的performance_schema数据库
replicate-do-db=ds0 #设置需要同步的数据库跟master一样
replicate-do-db=ds1 #设置需要同步的数据库跟master一样
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

重启mysql

service mysqld restart

重启mysql完成，登录slave的数据库执行如下命令：

CHANGE MASTER TO MASTER_HOST='192.168.16.128',MASTER_USER='slaveuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000005';

命令说明：

MASTER_HOST：是master机器的ip

MASTER_USER：slaveuser 是刚才在master创建用于同步的账号

MASTER_PASSWORD：123456 是刚才在master创建的密码

MASTER_LOG_FILE：是在master机器执行 show master status 出来的结果

开启slave执行：

START SLAVE;

查看slave状态执行：

show slave status ;

如果 Slave_IO_Running : No 或者 Slave_SQL_Running : No 则说明失败，可以查看执行以下命令查看详情

tail -n 500 /var/log/mysqld.log

解决方法可以参考：MHA集群报错：The slave I/O thread stops because master and slave have equal MySQL server UUIDs

解决后，master-slave 设置成功：

3，slave设置只读

set global read_only=1;  

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

为了确保所有用户，包括具有super权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令

flush tables with read lock; 

这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

4，测试

master 创建表Pack

CREATE TABLE `pack` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

slave刷新，也可以看到表哦