MySQL主从配置
环境准备
一主一从:
主库IP:192.168.192.133
从库IP:192.168.192.134
配置
主库操作:
-
修改my.cnf的配置文件
设置server-id值,并开启bin_log日志,重启mysql。
[[email protected] ~]# vim /etc/my.cnf log_bin=mysql server-id=101 #必须是集群中的唯一id [[email protected] ~]# systemctl restart mysqld
-
建立同步账号
[[email protected] ~]# mysql -uroot -pABCabc123! mysql> grant replication slave on *.* to 'repl'@192.168.192.134 identified by 'Aa123456.';
grant命令:
-
replication slave on,在主服务器新建一个用户赋予“replication slave on”的权限。你不需要再赋予其它的权限。
-
on:表示这些权限对哪些数据库和表生效
格式:数据库名.表名,这里写“*”表示所有数据库,所有表。如果我要指定将权限应用到test库的user表中,可以这么写:test.user -
to:将权限授予哪个用户。
格式:”用户名”@”登录IP或域名”。%表示没有限制,在任何主机都可以登录。比如:”repl”@”192.168.192.133”,表示repl这个用户只能在192.168.192.133登录。
一般不用root帐号,“%”表示该段所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.192.133,加强安全。 -
identified by:指定用户的登录密码
-
-
查看主库状态,记住file值和position值。
即当前日志文件名和二进制日志偏移量。
mysql> show master status; +--------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------+----------+--------------+------------------+-------------------+ | mysql.000004 | 904 | | | | +--------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
-
备份数据库数据
[[email protected] ~]# mysqldump -uroot -pABCabc123! -A -B |gzip > /date/mysql_alldb_$(date +%F).sql.gz; mysqldump: [Warning] Using a password on the command line interface can be insecure.
-
将主库备份的数据上传到从库
[[email protected] ~]# scp /date/mysql_alldb_2019-04-13.sql.gz 192.168.192.134:/date/mysql/ [email protected]'s password: mysql_alldb_2019-04-13.sql.gz 100% 205KB 14.7MB/s 00:00
从库操作:
-
修改my.cnf的配置文件
设置server-id值,并开启bin_log日志,重启mysql。
[[email protected] ~]# vim /etc/my.cnf [mysqld] server_id=102 [[email protected] ~]# systemctl restart mysqld
-
还原数据,通过从 主库传过来的备份数据来还原。
[[email protected] ~]# cd /date/mysql/ [[email protected] mysql]# ls mysql_alldb_2019-04-13.sql.gz [[email protected] mysql]# gzip -d mysql_alldb_2019-04-13.sql.gz [[email protected] mysql]# ls mysql_alldb_2019-04-13.sql [[email protected] ~]# mysql -urepl -pAa123456. < mysql_alldb_2019-04-13.sql #检查还原 [[email protected] ~]# mysql -urepl -pAa123456. -e 'show databases;'
-
设定从库同步主库
mysql> change master to -> master_host='192.168.192.133', -> master_port=3306, -> master_user='repl', -> master_password='Aa123456.', -> master_log_file='mysql.000004', -> master_log_pos=904; Query OK, 0 rows affected, 2 warnings (0.00 sec)
-
启动从库同步开关
mysql> start slave; Query OK, 0 rows affected (0.02 sec) #检查状态 mysql> show slave status\G;
出错:
接下来就可以测试了,在测试的时候需要注意,从库的数据要与主库数据一致,如果你只更改了从库的数据,这时候你再去更改主库的数据,从库不会发生变化。
查看从库的状态,会出现下面的情况:
解决方法:
-
解决方法1:
mysql> stop slave; mysql> set global sql_slave_skip_counter=1;
这个解决的是因为事务的原因导致的sql进程停止。
-
解决办法2:
mysql> stop slave; mysql> set global sql_slave_skip_counter=1; #在主库上查看: mysql> show master status; +--------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------+----------+--------------+------------------+-------------------+ | mysql.000006 | 1649 | | | | +--------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #在从库上重新手动同步: mysql> change master to -> master_host='192.168.192.133', -> master_port=3306, -> master_user='repl', -> master_password='Aa123456.', -> master_log_file='mysql.000006', -> master_log_pos=1649; Query OK, 0 rows affected, 2 warnings (0.00 sec)