主（192.168.0.214）--從（192.168.0.119）複製

兩臺機器mysql版本一致。

配置主庫/etc/my.cnf檔案

[root@dgryxrdb mysql]# less /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

server-id = 1 --設定ID，伺服器唯一ID，預設是1，一般取IP最後一段

log-bin = mysql-bin --啟動二進位制日誌，slave會基於此log-bin來做replication

binlog_format = mixed --指定二進位制日誌的記錄格式為混合模式（分為三種，statement：語句，row：行（事務），mixed：兩者混合）

./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking & --跳過授權啟動，訪問不需要讀取授權表

$mysql -u root -p mysql

update user set password=password("root") where user="root";

--5.7版本設定如下：

mysql> update user set authentication_string=password("root") where user="root"; ---貌似不需要吧，這是幹什麼的……

Query OK, 0 rows affected, 1 warning (0.01 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges; --重讀授權表

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO reuser@'192.168.%.%' IDENTIFIED BY 'repass'; --建立具有複製許可權的使用者，一般不用root賬戶，%表示任意值，可指定具體的IP，加強安全

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status --檢視二進位制日誌中事件的位置

-> ;

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

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

| mysql-bin.000007 | 154 | | | |

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

1 row in set (0.00 sec)

--執行完此步驟後，不要再操作主伺服器的Mysql，防止主伺服器狀態值發生變化（或者主庫增加讀鎖，等從庫資料匯入後再unlock tables）。

從庫配置：

配置my.cnf檔案

root@nfs mysql]# vi /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

server-id = 2

log-bin = mysql-bin

binlog_format = mixed

relay-log=/usr/local/mysql/data/relay-bin --指定中繼日誌的位置

read-only = 1 --開啟只讀模式

./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

$mysql -u root -p mysql

mysql> update user set authentication_string=password("root") where user="root";

Query OK, 0 rows affected, 1 warning (0.01 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> use mysql

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> set PASSWORD=PASSWORD('root'); --貌似大小寫敏感

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO reuser@'192.168.%.%' IDENTIFIED BY 'repass';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status; --檢視從伺服器的狀態資訊

mysql> show processlist; --檢視啟動執行緒

結果顯示沒啟動任何執行緒

配置從伺服器連線主伺服器

mysql> help change master to //檢視命令

--配置從伺服器連線主伺服器

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.214', MASTER_USER='reuser', MASTER_PASSWORD='repass', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;

Query OK, 0 rows affected, 1 warning (0.01 sec)

--啟動執行緒，從伺服器複製功能

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

--檢視伺服器工作狀態

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event --IO狀態

Master_Host: 192.168.0.214 --主伺服器IP

Master_User: reuser --具有複製許可權的使用者

Master_Port: 3306 --預設監聽的埠

Connect_Retry: 60 --重試時間

Master_Log_File: mysql-bin.000007 --讀取的二進位制檔案

Read_Master_Log_Pos: 154 --二進位制日誌中的位置，》=exec_master_log_pos

Relay_Log_File: relay-bin.000002 --當前讀取的中繼日誌檔案

Relay_Log_Pos: 320 --中繼日誌中事件的位置

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes --IO執行緒已啟動，必須為yes

Slave_SQL_Running: Yes --SQL執行緒已啟動，必須為yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 521

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0 --落後主伺服器的時間，單位為秒

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 30b5f2ea-e4c1-11e4-b0ef-000c294fd284

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

1 row in set (0.00 sec)

ERROR:

No query specified

主庫建立資料庫

mysql> create database mydb;

Query OK, 1 row affected (0.04 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| lqq_test |

| mydb |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.00 sec)

檢視從庫：

mysql> show databases;

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

| Database |

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

| information_schema |

| mydb |

| mysql |

| performance_schema |

| sys |

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

主-從之間的最初資料同步（資料初始化）

--主庫鎖表，防止再操作

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

進行資料備份

[mysql@leonfs data]$mysqldump -uroot -proot -P3306 -h 192.168.0.53 --all-databases >mysql53.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.[mysql@leonfs ~]$scp mysql53.sql mysql@192.168.0.119:/home/mysql

The authenticity of host '192.168.0.119 (192.168.0.119)' can't be established.

RSA key fingerprint is 3c:13:c9:11:d3:99:c1:c8:7c:e0:53:4c:73:d7:10:63.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.0.119' (RSA) to the list of known hosts.

mysql@192.168.0.119's password:

mysql53.sql

--從庫匯入備份

mysql> source /home/mysql/mysql53.sql

Mysql 主-從簡單配置---centos6.4

相關文章