mysql主从同步环境配置标准.pdf

MySQL 主从同步环境配置标准 /usr/local/mysql/bin/mysqladmin -u root password new-password /usr/local/mysql/bin/mysqladmin -u root -h 127.0.0.1 127.0.0.1 password new-password yum -y install ntpdate ntpdate cn.pool.ntp.org clock -w mysql 配置 1,master 配置 [mysqld] server-id=1 log-bin=mysql-bin #read-only=0 binlog-do-db=lqg_test #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行 binlog-ignore-db=mysql #不同步 mysql 系统数据库 binlog-ignore-db=test binlog-ignore-db=information_schema binlog-ignore-db=performance_schema 2,slave1 配置 [mysqld] server-id=2 log-bin=mysql-bin #read-only=1 replicate-do-db=lqg_test replicate-ignore-db=mysql #不同步 mysql 系统数据库 replicate-ignore-db=test replicate-ignore-db=information_schema replicate-ignore-db=performance_schema 设置密码 /usr/local/mysql/bin/mysqladmin -u root password Guoqing@161001 mysql -uroot -p Guoqing@161001 grant all on *.* to root@%identified by Guoguo@666Ft99; grant replication slave,reload,super on *.* to repl@10.170.4.91 identified by Shenzhen2016OK666 with grant option; flush privileges; grant replication slave,reload,super on *.* to repl@10.169.92.114 identified by Shenzhen2016OK666 with grant option; flush privileges; 在主库上执行 mysql show master status; +++++ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +++++ | mysql-bin.000005 | 921 | lqg_db,lqg_test | mysql,test,information_schema,performance_schema | +++++ 1 row in set (0.00 sec) mysql flush tables with read lock; Query OK, 0 rows affected (0.00 sec) 锁表 flush tables with read lock; #数据库只读锁定命令，防止导出数据库的时候有数据写入 在从库 slave01 上执行 mysql change master to master_host=10.170.25.100, master_user=repl, master_password=Shenzhen2016OK666, master_log_file=mysql-bin.000005, master_log_pos=921; 启动 slave 检测是否同步成功 mysql start slave; mysql show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes 正常了 在从库 slave02 上执行 mysql change master to master_host=10.170.25.100, master_user=repl, master_password=Shenzhen2016