[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表示啟動正常 ~~~