mysql replication配置_mysql replication配置

mysql replication配置

1、修改主機(master)的配置檔案(my.cnf)

server_id = 1

log_bin = mysql-bin

2、修改從機(slave)配置檔案

#主備環境下需要唯一

server_id = 2

3、主機建立複制分發使用者，并授權進行複制分發

CREATE USER repl IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repl';

4、重新啟動主機，使配置生效

5、擷取主機複制分發點

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

UNLOCK TABLES;

獲得類似如下結果

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 | 73 | test | manual,mysql |

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

其中file/position字段需要在從機使用

6、重新啟動從機

7、設定從機的主機資訊

語句類似如下

CHANGE MASTER TO MASTER_HOST='master_host_name',MASTER_USER='replication_user_name',MASTER_PASSWORD='replication_password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;

其中

MASTER_LOG_FILE='recorded_log_file_name'中recorded_log_file_name為show master status中的file字段内容

MASTER_LOG_POS=recorded_log_position中recorded_log_position為show master status中的Position字段内容

SHOW SLAVE STATUS得到類似如下内容

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

| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |

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

| Waiting for master to send event | 192.168.153.128 | repl | 3306 | 60 | mysql-bin.000019 | 800 | localhost-relay-bin.000003 | 676 | mysql-bin.000019 | Yes | Yes | | | | | | | 0 | | 0 | 800 | 853 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | aaf47eb1-fa78-11e3-a424-000c2932455f | /home/jiangyx/mysql/replication/slave/data/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 |

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

其中Slave_IO_Running / Slave_SQL_Running都為yes表示成功

注意：

1、如果slave是通過拷貝整個mysql的data，那麼需要修改data目錄下的auto.cnf檔案的uuid，以確定主機和備機的uuid不相同。否則show slave status中會有報錯資訊“Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. ”

2、出現如下錯誤，通過删除主機的mysql.user表中的空使用者(delete from mysql.user where user = '';)解決該問題

從機報告錯誤：2014-09-10 13:27:38 20653 [ERROR] Slave I/O: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1, Error_code: 1045

使用mysql連接配接主機：ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)

參考：

1、http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html