Centos配置MySQL主从数据库

在这之前首先确保虚拟机上安装了两个能够联网且知道IP的Centos7系统

1. 安装MySQL

1.1 安装下载

检查是否安装了mysql

rpm -qa | grep mysql

如果安装了请卸载

yum remove mysql*

因为centos-7默认是Mariadb,所以执行以下命令只是更新Mariadb数据库,这里要下载mysql的源

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

安装mysql源

rpm -ivh mysql-community-release-el7-5.noarch.rpm

安装mysql

yum install mysql-server

检查是否有错误

rpm -qa |grep mysql 

如果内容中有

Error: Package: mysql-community-libs-5.6.35-2.el7.x86_64 (mysql56-community)
           Requires: libc.so.6(GLIBC_2.17)(64bit)
Error: Package: mysql-community-server-5.6.35-2.el7.x86_64 (mysql56-community)
           Requires: libc.so.6(GLIBC_2.17)(64bit)
Error: Package: mysql-community-server-5.6.35-2.el7.x86_64 (mysql56-community)
           Requires: systemd
Error: Package: mysql-community-server-5.6.35-2.el7.x86_64 (mysql56-community)
           Requires: libstdc++.so.6(GLIBCXX_3.4.15)(64bit)
Error: Package: mysql-community-client-5.6.35-2.el7.x86_64 (mysql56-community)
           Requires: libc.so.6(GLIBC_2.17)(64bit)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

解决办法:

yum install glibc.i686
yum list libstdc++*

1.2 配置登录

此时进行登陆

mysql -u root 

登录时有可能报这样的错:ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2),原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户:

sudo chown root /var/lib/mysql

编辑MySQL配置

vi /etc/my.cnf

注释配置文件

#socket=/var/lib/mysql/mysql.sock
修改过后的my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启mysqld服务

service mysqld restart

登陆mysql进行设置密码

#登陆
mysql -u root
#切换数据库
use mysql;
#设置登陆密码
update user set password=password('123456') where user='root';
#退出
exit;

重启mysqld服务

service mysqld restart

如果需要远程连接数据库,需要登陆数据库进行设置,BY后面的字符是远程登陆的密码

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";

此时可能还连接不上,因为防火墙的原因,需要开放3306端口

#centos7 3306端口通行并重启
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
#iptables 3306端口通行
vim /etc/sysconfig/iptables#编辑防火墙文本
#添加规则
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
#重启
service iptables restart

2. 配置主从数据库

2.1 设置master数据库

修改master机器中mysql配置文件my.cnf,该文件在/etc目录下

vi /etc/my.cnf

在[mysqld]配置段添加如下字段

server-id=1
log-bin=mysql-bin
log-slave-updates=1 #slave数据库个数
binlog-do-db=dong  #需要同步的数据库,如果没有本行表示同步所有的数据库
binlog-ignore-db=mysql  #被忽略的数据

登录mysql数据库,执行数据库命令,在master机上为slave机添加一同步帐号, 此处我的slave的数据库地址为192.168.0.222,根据自己不同的ip进行设置,@前面是用户名称,BY后面是密码

grant replication slave on *.* to 'root'@'192.168.0.222' identified by '123456';
flush  privileges;

show master status 命令看日志情况

mysql> show master status;
#显示内容
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 | dong         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

通过该命令获得FilePosition,在slave中有用 。注:基准这里的mysql-bin.000001120,在下面设置Slave复制”的配置中会用到

2.2 设置slave数据库

修改slave机器中mysql配置文件my.cnf,该文件在/etc目录下

vi /etc/my.cnf

在[mysqld]配置段添加如下字段

server-id=2
log-bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1
log-slave-updates=1
replicate-do-db=dong #要同步的数据库,不写本行表示同步所有数据库

然后重启slave机的mysql

service mysqld restart

登录slave的mysql数据库,设置slave复制 此处的FilePosition与Master

CHANGE MASTER TO
MASTER_HOST='192.168.0.221',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120,
MASTER_CONNECT_RETRY=10;

启动slave

start slave;

主要查看Slave_IO_RunningSlave_SQL_Running 两列是否都为YES

mysql> 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.0.221 | root        |        3306 |            10 | mysql-bin.000001 |                 120 | mysql-relay-bin.000002 |           283 | mysql-bin.000001      | Yes              | Yes               | dong            |                     |                    |                        |                         |                             |          0 |            |            0 |                 120 |             456 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | 1d004057-2f20-11e8-90d2-080027f0e6a5 | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for the slave I/O thread to update it |              86400 |             |                         |                          |                |                    |                    |                   |             0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
1 row in set (0.01 sec)

2.3 测试

在master数据库创建表,写入表数据,查看slave表是否已经复制过来了; 下面是在master写入的数据的查询结果

mysql> use dong;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | admin |
+----+-------+
1 row in set (0.00 sec)

在slave表中查询是否存在,若数据一致,则主从搭建成功。

3. 扩展

同步所有主库所有数据库

注释master和slave的my.cnf配置文件中 master注释行

#binlog-do-db=dong
slave注释行

#replicate-do-db=dong

log-slave-updates

log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志,这个选项默认是不打开的,但是,如果这个从服务器B是服务器A的从服务器,同时还作为服务器C的主服务器,那么就需要开发这个选项,这样它的从服务器C才能获得它的二进制日志进行同步操作

master-connect-retry

master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒

read-only

read-only是用来限制普通用户对从数据库的更新操作,以确保从数据库的安全性,不过如果是超级用户依然可以对从数据库进行更新操作

slave-skip-errors

在复制过程中,由于各种的原因,从服务器可能会遇到执行BINLOG中的SQL出错的情况,在默认情况下,服务器会停止复制进程,不再进行同步,等到用户自行来处理。Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号,当复制过程中遇到定义的错误号,就可以自动跳过,直接执行后面的SQL语句。

--slave-skip-errors=[err1,err2,…….|ALL]

但必须注意的是,启动这个参数,如果处理不当,很可能造成主从数据库的数据不同步,在应用中需要根据实际情况,如果对数据完整性要求不是很严格,那么这个选项确实可以减轻维护的成本

reset slave all

用于清除slave

# MySQL 

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×