MySQL主從搭建與配置

發(fā)布時(shí)間：2020-05-24 23:52:09 來源：網(wǎng)絡(luò) 閱讀：378 作者：Rachy1989 欄目：數(shù)據(jù)庫

MySQL主從（MySQL replication），主要用于MySQL的實(shí)時(shí)備份或者讀寫分離。在配置之前先做一下準(zhǔn)備工作，配置兩臺(tái)MySQL服務(wù)器，如果你的機(jī)器不能同時(shí)跑兩臺(tái)Linux虛擬機(jī)，那么可以考慮在同一個(gè)機(jī)器上跑兩個(gè)MySQL服務(wù)。

MySQL主從的原理非常簡單，總結(jié)一下：

（1）每個(gè)主從僅可以設(shè)置一個(gè)主。

（2）主在執(zhí)行SQL之后，記錄二進(jìn)制log文件（bin-log）

（3）從連接主，并獲取主的bin-log，存于本地relay-log，并從上次執(zhí)行的位置起執(zhí)行SQL，一旦遇到錯(cuò)誤則停止同步。

mysql主從配置replication，又叫A,B復(fù)制，保證主從數(shù)據(jù)同步

A --> change data --> bin_log -transfer --> B --> repl_log -->change data

從這幾條replication原理來看，可以有這些推論：

（1）主從間的數(shù)據(jù)庫不是實(shí)時(shí)同步，就算網(wǎng)絡(luò)連接正常，也存在瞬間，主從數(shù)據(jù)不一致。

（2）如果主從的網(wǎng)絡(luò)斷開，從會(huì)在網(wǎng)絡(luò)正常后，批量同步。

（3）如果對(duì)從進(jìn)行修改數(shù)據(jù)，那么很可能從在執(zhí)行主的bin-log時(shí)出現(xiàn)錯(cuò)誤而停止同步，這是個(gè)很危險(xiǎn)的操作。所以一般情況下，非常小心的修改從上的數(shù)據(jù)。

（4）一個(gè)衍生的配置是雙主，即互為主從配置，只要雙方的修改不沖突，可以工作良好。

（5）如果需要多主的話，可以用環(huán)形配置，這樣任何一個(gè)節(jié)點(diǎn)的修改都可以同步到所有節(jié)點(diǎn)。

（6）可以應(yīng)用在讀寫分離的場景，用以降低單臺(tái)MySQL的I/O

（7）可以是一主多從，也可以是相互主從（主主）

主MySQL（master）：192.168.134.128

從MySQL（slave）：192.168.134.129

1.準(zhǔn)備工作：

（1）修改兩個(gè)主機(jī)的主機(jī)名：

主：192.168.134.128

[root@master ~]# hostname master

[root@master ~]# vim /etc/sysconfig/network

hostname=master

從：192.168.134.129

[root@slave~]# hostname slave

[root@slave~]# vim /etc/sysconfig/network

hostname=slave

（2）在兩臺(tái)機(jī)器上編輯hosts文件：

vim /etc/hosts

都加入兩行：

192.168.134.128 master

192.168.134.129 slave

（3）關(guān)閉兩臺(tái)機(jī)器的防火墻：

關(guān)閉SELinux：

setenforce 0

vim /etc/selinux/config

SELINUX=disabled

關(guān)閉iptables：

iptables -F

iptables-save

chkconfig iptables off

2.在主從上都安裝MySQL：

主：192.168.134.128

進(jìn)入源碼包目錄：

[root@master ~]# cd /usr/local/src

下載MySQL安裝包：

[root@master src]# ls

mysql-5.1.73-linux-x86_64-glibc23.tar.gz

解壓MySQL包：

[root@master src]# tar zxvf mysql-5.1.73-linux-x86_64-glibc23.tar.gz

移動(dòng)并重命名安裝目錄：

[root@master src]# mv mysql-5.1.73-linux-x86_64-glibc23 /usr/local/mysql

查看安裝目錄內(nèi)容：

[root@master src]# ls /usr/local/mysql/

bin data include lib mysql-test scripts sql-bench

COPYING docs INSTALL-BINARY man README share support-files

創(chuàng)建mysql用戶，不讓其登錄：

[root@master src]# useradd -s /sbin/nologin mysql

進(jìn)入安裝目錄：

[root@master src]# cd /usr/local/mysql/

拷貝配置文件到/etc目錄下覆蓋原來的my.cnf：

[root@master mysql]# cp support-files/my-small.cnf /etc/my.cnf

cp：是否覆蓋"/etc/my.cnf"？ y

拷貝啟動(dòng)腳本到/etc/init.d/目錄下重命名為mysqld：

[root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld

編輯啟動(dòng)腳本：

[root@master mysql]# vim /etc/init.d/mysqld

定義basedir和datadir:

basedir=/usr/local/mysql

datadir=/data/mysql

創(chuàng)建數(shù)據(jù)庫存放路徑：

[root@master mysql]# mkdir /data/mysql

配置：

[root@master mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

WARNING: The host 'master' could not be looked up with resolveip.

This probably means that your libc libraries are not 100 % compatible

with this binary MySQL version. The MySQL daemon, mysqld, should work

normally with the exception that host name resolving will not work.

This means that you should use IP addresses instead of hostnames

when specifying MySQL privileges !

Installing MySQL system tables...

170312 23:59:44 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

Filling help tables...

170312 23:59:44 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

出現(xiàn)兩個(gè)OK表示配置成功。

啟動(dòng)MySQL：

[root@master mysql]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

查看進(jìn)程：

[root@master mysql]# ps aux | grep mysql

root 1369 0.2 0.0 106060 1484 pts/0 S 01:00 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid

mysql 1481 1.5 0.5 265280 21612 pts/0 Sl 01:00 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql --log-error=/data/mysql/master.err --pid-file=/data/mysql/master.pid --socket=/tmp/mysql.sock --port=3306

root 1494 0.0 0.0 103248 872 pts/0 S+ 01:00 0:00 grep mysql

查看端口：

[root@master mysql]# netstat -lnp | grep mysql

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1481/mysqld

unix 2 [ ACC ] STREAM LISTENING 18672 1481/mysqld /tmp/mysql.sock

從：192.168.134.129（主從MySQL的安裝配置過程一模一樣，這里不再贅述）

登錄MySQL有三種方式：

1.使用絕對(duì)路徑登錄：

/usr/local/mysql/bin/mysql

2.使用socket登錄：

mysql -S /tmp/mysql.sock

3.使用host+port登錄：

mysql -h227.0.0.1 -P3306

默認(rèn)都是沒有密碼的，可以使用mysqladmin設(shè)置密碼。

3.開始搭建主從MySQL：

主：192.168.134.128

將MySQL加入到環(huán)境變量中：

[root@master mysql]# vim /etc/profile.d/mypath.sh

export PATH=$PATH:/usr/local/mysql/bin/

[root@master mysql]# source /etc/profile.d/mypath.sh

登錄MySQL創(chuàng)建數(shù)據(jù)庫db1：

[root@master mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.1.73 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

先退出mysql：

mysql> quit

拷貝mysql庫到db1庫：

備份到123.sql：

[root@master mysql]# mysqldump -S /tmp/mysql.sock mysql > 123.sql

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

恢復(fù)到db1：

[root@master mysql]# mysql -S /tmp/mysql.sock db1 < 123.sql

再次登錄MySQL，查看db1中的內(nèi)容：

[root@master mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.73 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use db1;

Database changed

mysql> show tables;

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

| Tables_in_db1 |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

23 rows in set (0.00 sec)

mysql> quit

Bye

說明db1數(shù)據(jù)庫創(chuàng)建成功。

編譯配置文件：

[root@master mysql]# vim /etc/my.cnf

打開log-bin前面的注釋：

log-bin=mysql-bin

并在其下面添加一行（表示只對(duì)db1做主從）：

binlog-do-db=db1

（多個(gè)數(shù)據(jù)可以用逗號(hào)分隔：binlog-do-db=db1,db2,db3，或者使用黑名單形式：binlog-ignore-db=db1）

重啟MySQL：

[root@master mysql]# /etc/init.d/mysqld restart

Shutting down MySQL... SUCCESS!

Starting MySQL. SUCCESS!

查看/data/mysql/下的內(nèi)容：

[root@master mysql]# ls /data/mysql

db1 ibdata1 ib_logfile0 ib_logfile1 master.err master.pid mysql mysql-bin.000001 mysql-bin.index test

發(fā)現(xiàn)二進(jìn)制日志文件mysql-bin.000001已經(jīng)生成。

登錄mysql：

[root@master mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.1.73-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

以replication權(quán)限授權(quán)給從MySQL上一個(gè)用戶slave密碼123abc：

mysql> grant replication slave on *.* to 'slave'@'192.168.134.129' identified by '123abc';

Query OK, 0 rows affected (0.00 sec)

刷新權(quán)限：

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

先把表鎖起來：

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

顯示主MySQL的狀態(tài)：

mysql> show master status;

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

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

| mysql-bin.000001 | 338 | db1 | |

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

1 row in set (0.00 sec)

從：192.168.134.129

將MySQL加入到環(huán)境變量中：

[root@slave mysql]# vim /etc/profile.d/mypath.sh

export PATH=$PATH:/usr/local/mysql/bin/

[root@slave mysql]# source /etc/profile.d/mypath.sh

編輯配置文件：

[root@slave mysql]# vim /etc/my.cnf

保證server-id不與主的相同即可：

server-id = 2

（主的server-id = 1）

重啟：

[root@slave mysql]# /etc/init.d/mysqld restart

Shutting down MySQL..... SUCCESS!

Starting MySQL. SUCCESS!

在從上也創(chuàng)建庫db1，

[root@slave mysql]# mysql -e "create database db1"

先將主上備份的123.sql拷貝到從上/usr/local/mysql目錄下：

[root@slave mysql]# scp root@192.168.134.128:/usr/local/mysql/123.sql /usr/local/mysql/123.sql

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

RSA key fingerprint is 7d:f3:cc:4e:ae:cb:3c:31:61:d5:13:8e:04:dc:73:02.

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

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

root@192.168.134.128's password:

123.sql

將123.sql恢復(fù)到db1：

[root@slave mysql]# mysql db1 < 123.sql

保證主從上的數(shù)據(jù)庫一樣：

登錄mysql先停掉slave：

[root@slave mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.1.73 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> slave stop;

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

配置主從關(guān)系（非常關(guān)鍵）：

mysql> change master to master_host='192.168.134.128',master_port=3306,master_user='slave',master_password='123abc',master_log_file='mysql-bin.000001',master_log_pos=338;

Query OK, 0 rows affected (0.42 sec)

開啟slave：

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

查看slave狀態(tài)，顯示兩個(gè)Yes即為配置成功：

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.134.128

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 338

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: 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: 338

Relay_Log_Space: 406

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:

1 row in set (0.00 sec)

ERROR:

No query specified

4.測試MySQL主從：MySQL主從不可以在從上操作，一旦在從上執(zhí)行一些寫入操作的話，主從機(jī)制會(huì)發(fā)生紊亂。

測試1：在主上刪除一個(gè)表，從上也會(huì)刪除：

主：192.168.134.128

[root@master mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.73-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

解鎖表：

mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

使用db1：

mysql> use db1;

Database changed

查看表：

mysql> show tables;

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

| Tables_in_db1 |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

23 rows in set (0.00 sec)

刪除表：

mysql> drop table help_category ;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

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

| Tables_in_db1 |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

22 rows in set (0.00 sec)

從：192.168.134.129

[root@slave mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.1.73 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

使用db1：

mysql> use db1;

Database changed

查看表：

mysql> show tables;

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

| Tables_in_db1 |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

22 rows in set (0.00 sec)

可以看到從上help_category 也被刪除了。

測試2：在主上創(chuàng)建一個(gè)表，從上也會(huì)創(chuàng)建：

主：192.168.134.128

創(chuàng)建表：

mysql> create table tb1 (`id` int(4),`name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

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

| Tables_in_db1 |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| tb1 |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

23 rows in set (0.00 sec)

從：192.168.134.129

mysql> show tables;

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

| Tables_in_db1 |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| tb1 |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

23 rows in set (0.00 sec)

可以看到剛創(chuàng)建的表。

測試3：在主上刪除庫，從上也不能再使用

主：192.168.134.128

刪除庫：

mysql> drop database db1;

Query OK, 23 rows affected (0.01 sec)

mysql> show tables;

ERROR 1046 (3D000): No database selected

查看庫：mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| test |

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

3 rows in set (0.00 sec)

從：192.168.134.129

mysql> show tables;

ERROR 1049 (42000): Unknown database 'db1'

報(bào)錯(cuò)：Unknown database 'db1'

查看庫：

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| test |

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

3 rows in set (0.00 sec)

可以看到也沒有db1庫了。

向AI問一下細(xì)節(jié)

MySQL主從搭建與配置

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽