mysql伺服器主從同步設定

1、說明

   主伺服器：192.168.0.17  

   從伺服器：192.168.0.68   建立的一個從伺服器，需要同步主伺服器資料。

2、配置

   (1)、主伺服器配置192.168.0.17

       #vim /etc/my.cnf      加入以下幾行。

       server-id = 1105161846

       log-bin=mysql-bin

       log_bin_index = mysql-bin.index

       binlog_do_db = test_db

       sync_binlog = 1

       重新開機mysql服務

       #/etc/init.d/mysqld restart

       #mysql -uroot -p

       mysql>show master status;

       +------------------+----------+--------------+------------------+

       | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

       | mysql-bin.000038 | 32430008 | test_db      |                  |

       1 row in set (0.00 sec)

       建立複制賬号

       mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.0.68' IDENTIFIED BY "123456"; 

       mysql>flush privileges;

   (2)、從伺服器配置192.168.0.68

        #vim /etc/my.cnf  加入以下幾行。

        log-bin=mysql-bin

        log_bin_index       = mysql-bin.index

        log_slave_updates

        relay_log           = mysql-relay-bin

        relay_log_index     = mysql-relay-bin.index

        max_binlog_size     = 200M

        slave-skip-errors   = 1062,1053

        skip_slave_start    = 1

        expire_logs_days    = 7

        sync_binlog         = 100      

        把主資料庫的db導入從資料庫

        #mysqldump -h192.168.0.17 -u test_db -p ‘123456’ -R --triggers  --single-transaction --flush-logs --default-character-set=utf8 --master-data --database test_db > test_db.sql        

        #mysql -uroot -p -e "source < ~/test_db.sql"

        設定同步

        #mysql -uroot -p

        mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.0.17' IDENTIFIED BY "123456";

        mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.17',

            -> MASTER_PORT=3306,

            -> MASTER_USER='repluser',

            -> MASTER_PASSWORD='123456',

            -> MASTER_LOG_FILE='mysql-bin.000038',

            -> MASTER_LOG_POS=32430008;

        mysql>slave start;

        mysql>show slave status\G;

        .......

              Master_Log_File: mysql-bin.000038

          Read_Master_Log_Pos: 37762194

               Relay_Log_File: mysql-relay-bin.000004

                Relay_Log_Pos: 25417693

        Relay_Master_Log_File: mysql-bin.000038

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

        ........

表示同步成功！

 清除binlog

mysql>PURGE MASTER LOGS TO 'mysql-bin.000243';

愛慕爾商城歡迎您的光臨！

穿衣打扮

城市物語