手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL主从搭建与配置

[日期:2017-03-26] 来源:Linux社区  作者:rachy [字体: ]

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

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.

OK

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

出现两个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 -h127.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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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库了。

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-03/142193.htm

linux
相关资讯       MySQL主从 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款