MySQL 5.7主主备份配置
MySQL 5.7主主备份配置
1. 主要配置步骤
主库配置步骤:
1、GRANT创建用户并授权,ip为从服务器的ip,本句含义是为创建一个用户名为uname,密码为upwd的用户,这个用户只能从192.168.1.111上进行访问
mysql> grant replication slave on *.* to 'repl_user'@'192.168.3.115' identified by 'zcxc123'; 2 Query OK, 0 rows affected (0.01 sec)
2、修改my.cnf配置文件如下:
log-bin=mysql-bin #启动二进制文件 2 server_id=1 #服务器ID
3、重启mysql
此时可以查看主服务器binlog日志position值
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000080 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
4、锁定所有表 mysql> FLUSH TABLES WITH READ LOCK;
5、备份表 [root@localhost mysql]# mysqldump -uroot -p --all-databases -l -F >all_db.sql
6、解锁 mysql> UNLOCK TABLES;
7、把数据传到从库(192.168.3.115) # scp all_db.sql [email protected]:/tmp
从库配置步骤:
1、修改从服务器my.cnf配置文件
log_bin = mysql server_id = 2
2、重启mysql服务器
service mysqld restar
3、导入主备份文件
# mysql -uroot -p </tmp/all_db.sql
4、同步binlog日志
mysql> reset slave; Query OK, 0 rows affected (0.00 sec)
注:master_user='repl_user',master_password='zcxc123' 是主库第一步 grant replication 语句设置的 master_log_file='mysql-bin.000080',master_log_pos=154 是主库第三步show master status\G语句获取的 mysql> change master to master_host='192.168.3.116',master_user='repl_user',master_password='zcxc123',master_log_file='mysql-bin.000080',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec)
主主配置就是,按照以上步骤,把上面从库按主库配置一遍。再配置时 不用备份表了。
2. 配置文件
配置文件1
-bash-4.1# more /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock validate_password=OFF server-id=1 user=mysql log-bin=mysql-bin log-slave-updates slave-skip-errors=all sync_binlog=1 auto-increment-increment = 1 auto-increment-offset = 1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 relay_log=/var/lib/mysql/mysql-relay-bin log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -bash-4.1#
配置文件2
-bash-4.1# more /etc/my.cnf
相关推荐
CoderToy 2020-11-16
emmm00 2020-11-17
王艺强 2020-11-17
ribavnu 2020-11-16
bianruifeng 2020-11-16
wangshuangbao 2020-11-13
苏康申 2020-11-13
vivenwan 2020-11-13
moyekongling 2020-11-13
云中舞步 2020-11-12
要啥自行车一把梭 2020-11-12
aydh 2020-11-12
kuwoyinlehe 2020-11-12
minerk 2020-11-12
vitasfly 2020-11-12
jazywoo在路上 2020-11-11
敏敏张 2020-11-11
世樹 2020-11-11