[TOC] ## 2. 主从搭建 2.1 修改主服务器配置 ~~~ vim /etc/my.cnf 在【mysqld】添加 server-id=1 log-bin=mysqlmaster-bin.log sync_binlog=1 innodb_buffer_pool_size = 1024M innodb_flush_log_at_trx_commit=1 sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO lower_case_table_names=1 log_bin_trust_function_creators=1 ~~~ 2.2 修改从服务器配置 ~~~ vim /etc/my.cnf 在【mysqld】添加 server-id=2 log-bin=mysqlslave-bin.log sync_binlog=1 innodb_buffer_pool_size = 1024M innodb_flush_log_at_trx_commit=1 sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO lower_case_table_names=1 log_bin_trust_function_creators=1 ~~~ 2.3 重启主、从服务器的mysql服务 ` service mysql restart` 2.4登录主服务器mysql ~~~ GRANT REPLICATION SLAVE ON *.* TO 'systop1'@'192.168.%' IDENTIFIED BY 'systop'; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 记下： file = mysqlmaster-bin.000001和 position = 329 ~~~ 2.5 导出主mysql数据 ~~~ mysqldump -uroot -p -P3306 --all-databases --triggers --routines --events >mysqlall.sql ~~~ 2.6 解锁 ~~~ UNLOCK TABLES; ~~~ 2.7 将数据文件远程拷贝到从服务器 ~~~ scp mysqlall.sql 192.168.X.X:/tmp/ ~~~ 2.8 从服务器导入数据 ~~~ mysql -uroot -p -h127.0.0.1 -P3306 < /tmp/mysqlall.sql ~~~ 2.9 从服务器执行 ~~~ CHANGE MASTER TO MASTER_HOST='192.168.4.101', MASTER_USER='systop1',MASTER_PASSWORD='systop',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=120; ~~~ 2.10 从服务器执行 ~~~ start slave; show slave status \G; 查看: Slave_IO_Running: Yes Slave_SQL_Running: Yes 都显示yes表示启动正常 ~~~