选一个网站做seo,南开集团网站建设,四川建设网官网住房和城乡厅官网,做网站要多少钱新乡mysql数据库级联同步配置步骤#xff0c;本文以一台mysql数据库多实例3306、3307和3309为例进行配置#xff0c;3306为主库#xff0c;3307为从库#xff0c;3309为子从库(多台单实例与一台多实例配置是一样的)一.my.cnf文件配置1.三个数据库实例修改my.cnf配置文件#x…mysql数据库级联同步配置步骤本文以一台mysql数据库多实例3306、3307和3309为例进行配置3306为主库3307为从库3309为子从库(多台单实例与一台多实例配置是一样的)一.my.cnf文件配置1.三个数据库实例修改my.cnf配置文件主库3306和从库3307要打开log-bin三个server-id不能一样[rootmysql ~]# egreplog-bin|server-id /data/{3306,3307,3309}/my.cnf/data/3306/my.cnf:log-bin /data/3306/mysql-bin/data/3306/my.cnf:server-id 1/data/3307/my.cnf:log-bin /data/3307/mysql-bin/data/3307/my.cnf:server-id 3/data/3307/my.cnf:#log-bin /data/3309/mysql-bin/data/3307/my.cnf:server-id 52.从库3307的my.cnf文件的[mysqld]模块里添加一条内容log-slave-updates3.重启数据库3306、3307和3309[rootmysql ~]#/data/3306/mysql stop[rootmysql ~]#/data/3306/mysql start[rootmysql ~]# /data/3307/mysqlstop[rootmysql ~]# /data/3307/mysqlstart[rootmysql ~]# /data/3309/mysqlstop[rootmysql ~]# /data/3309/mysqlstart4.进入主数据库3306查询log_bin是否打开server_id是多少,3307进行同样操作[rootmysql ~]# mysql -uroot-p123456 -S /data/3306/mysql.sockmysql show variables likelog_bin;----------------------| Variable_name | Value |----------------------| log_bin | ON |----------------------1 row in set (0.01 sec)mysql show variables likeserver_id;----------------------| Variable_name | Value |----------------------| server_id | 1 |----------------------1 row in set (0.00 sec)5.在主库3306上创建一个专门用来同步数据库的用户mysql grant replicationslave on *.* to rep10.0.0.% identified by 123456;###*.*代表所有库和所有表mysql flush privileges;mysql select user,hostmysql.user;mysql show grants forrep10.0.0.%;二.主库3306备份数据导入从库33071.在主库3306上备份数据[rootmysql ~]# mysqldump-uroot -p123456 -S /data/3306/mysql.sock -A --events -B -x --master-data1|gzip/opt/$(date %F).sql.gz-A:所有的库 -x:全局锁表2.将数据导入从库3307[rootmysql ~]# ls /opt2017-07-03.sql.gz[rootmysql ~]# cd /opt[rootmysql opt]# gzip -d2017-07-03.sql.gz[rootmysql opt]# ls2017-07-03.sql[rootmysql opt]# mysql -uroot-p123456 -S /data/3307/mysql.sock 2017-07-03.sql3.从库3307连接主库3306[rootmysql opt]# mysql -uroot-p123456 -S /data/3309/mysql.sock CHANGE MASTER TOMASTER_HOST10.0.0.20,MASTER_PORT3307,MASTER_USERrep,MASTER_PASSWORD123456;EOF[rootmysql opt]# mysql -uroot-p123456 -S /data/3307/mysql.sock -e start slave三.从库3307备份数据导入从库3309步骤同上一样四.测试在主库3306上创建数据库然后再登陆3307和3309查看是否正常同步,经测试已可以正常同步了^_^
