MySQL主从搭建与配置

MySQL主从（MySQL replication），主要用于MySQL的实时备份或者读写分离。在配置之前先做一下准备工作，配置两台MySQL服务器，如果你的机器不能同时跑两台Linux虚拟机，那么可以考虑在同一个机器上跑两个MySQL服务。

创新互联建站自2013年起，是专业互联网技术服务公司，拥有项目成都做网站、网站建设、外贸营销网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元江汉做网站,已为上家服务,为江汉各地企业和个人服务,联系电话:028-86922220

MySQL主从的原理非常简单，总结一下：

（1）每个主从仅可以设置一个主。

（2）主在执行SQL之后，记录二进制log文件（bin-log）

（3）从连接主，并获取主的bin-log，存于本地relay-log，并从上次执行的位置起执行SQL，一旦遇到错误则停止同步。

mysql主从配置replication，又叫A,B复制，保证主从数据同步

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

从这几条replication原理来看，可以有这些推论：

（1）主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。

（2）如果主从的网络断开，从会在网络正常后，批量同步。

（3）如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这是个很危险的操作。所以一般情况下，非常小心的修改从上的数据。

（4）一个衍生的配置是双主，即互为主从配置，只要双方的修改不冲突，可以工作良好。

（5）如果需要多主的话，可以用环形配置，这样任何一个节点的修改都可以同步到所有节点。

（6）可以应用在读写分离的场景，用以降低单台MySQL的I/O

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

主MySQL（master）：192.168.134.128

从MySQL（slave）：192.168.134.129

1.准备工作：

（1）修改两个主机的主机名：

主：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）在两台机器上编辑hosts文件：

vim /etc/hosts

都加入两行：

192.168.134.128 master

192.168.134.129 slave

（3）关闭两台机器的防火墙：

关闭SELinux：

setenforce 0

vim /etc/selinux/config

SELINUX=disabled

关闭iptables：

iptables -F

iptables-save

chkconfig iptables off

2.在主从上都安装MySQL：

主：192.168.134.128

进入源码包目录：

[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

移动并重命名安装目录：

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

查看安装目录内容：

[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

创建mysql用户，不让其登录：

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

进入安装目录：

[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

拷贝启动脚本到/etc/init.d/目录下重命名为mysqld：

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

编辑启动脚本：

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

定义basedir和datadir:

basedir=/usr/local/mysql

datadir=/data/mysql

创建数据库存放路径：

[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.

出现两个OK表示配置成功。

启动MySQL：

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

Starting MySQL. SUCCESS!

查看进程：

[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.使用绝对路径登录：

/usr/local/mysql/bin/mysql

2.使用socket登录：

mysql -S /tmp/mysql.sock

3.使用host+port登录：

mysql -h227.0.0.1 -P3306

默认都是没有密码的，可以使用mysqladmin设置密码。

3.开始搭建主从MySQL：

主：192.168.134.128

将MySQL加入到环境变量中：

[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创建数据库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.

恢复到db1：

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

再次登录MySQL，查看db1中的内容：

[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数据库创建成功。

编译配置文件：

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

打开log-bin前面的注释：

log-bin=mysql-bin

并在其下面添加一行（表示只对db1做主从）：

binlog-do-db=db1

（多个数据可以用逗号分隔：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/下的内容：

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

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

发现二进制日志文件mysql-bin.000001已经生成。

登录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权限授权给从MySQL上一个用户slave密码123abc：

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

Query OK, 0 rows affected (0.00 sec)

刷新权限：

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的状态：

mysql> show master status;

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

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

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

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

1 row in set (0.00 sec)

从：192.168.134.129

将MySQL加入到环境变量中：

[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!

在从上也创建库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恢复到db1：

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

保证主从上的数据库一样：

登录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)

配置主从关系（非常关键）：

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状态，显示两个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主从不可以在从上操作，一旦在从上执行一些写入操作的话，主从机制会发生紊乱。

测试1：在主上删除一个表，从上也会删除：

主：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 tablehelp_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：在主上创建一个表，从上也会创建：

主：192.168.134.128

创建表：

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)

可以看到刚创建的表。

测试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'

报错：Unknown database 'db1'

查看库：

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| test |

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

3 rows in set (0.00 sec)

可以看到也没有db1库了。

网站栏目：MySQL主从搭建与配置
本文路径：http://6mz.cn/article/pphejs.html

网站建设知识

MySQL主从搭建与配置

其他资讯