MySQL-Cluster

  • 操作系统环境

操作系统环境rhel6.5操作系统自带的软件包
两台主机:server7.com以及server8.com

  • 首先安装mysql-server.x86_64 0:5.1.71-1.el6这里是系统自带的版本
[root@server7 ~]# yum install mysql-server -y
[root@server7 ~]# /etc/init.d/mysqld start
  • 并且会自动执行Mysql的初始化操作;
  • 修改server7.com上面的配置文件为
在[mysqld]里面添加
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
  • 然后启动主节点上面的服务,这样mysqld的二进制日志文件就会更新
[root@server7 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
  • 查看更新的二进制日志文件
[root@server7 ~]# ll /var/lib/mysql/
total 20496
-rw-rw----. 1 mysql mysql      106 3月  27 08:48 mysql-bin.000001
-rw-rw----. 1 mysql mysql       19 3月  27 08:48 mysql-bin.index
....
  • 如果再次重新启动mysqld那么服务的配置文件就会再次更新
-rw-rw----. 1 mysql mysql      125 3月  27 08:50 mysql-bin.000001
-rw-rw----. 1 mysql mysql      106 3月  27 08:51 mysql-bin.000002
-rw-rw----. 1 mysql mysql       38 3月  27 08:51 mysql-bin.index
....
  • 在进行数据库的主从复制,依赖的就是这些二进制日志文件
  • 地写的这些信息表示的是mysqld正在使用的mysql二进制日志的文件以及开始的位置;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
  • 需要对远程用户进行授权
mysql> grant replication slave on *.* to linux@'172.25.23.8' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec
  • 接下来在另一个节点上面安装mysqld,尝试使用联系远程主机
[root@server8 ~]# yum install mysql-server -y
[root@server8 ~]# mysql -h 172.25.23.7 -ulinux -p
  • 如果出现这个错误
[root@server8 ~]# mysql -h 172.25.23.7 -ulinux -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.23.7' (113)
  • 通常表示的是网络问题,例如防火墙端口或者网络是否可达
  • 更改server8.com的配置文件
[root@server8 ~]# vim /etc/my.cnf 
添加:
server-id=2
  • 然后重新启动服务
[root@server8 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
  • 在从节点上面,说明开始进行二进制日志同步的用户,密码,二进制日志的文件的名称以及位置,这个命令必须在mysql的管理员账户完成,否则体是权限不足
mysql> show slave status;
ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
  • 使用mysql的管理员密码登陆,默认密码为空,进行登陆,然后执行下面的操作
mysql> change master to master_host='172.25.23.7',master_user='linux',master_password='westos',master_log_file='mysql-bin.000002', master_log_pos=332;
Query OK, 0 rows affected (0.87 sec)

  • 然后启动从服务器
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
  • 通过命令查看slave的状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.23.7
                  Master_User: linux
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 332
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
             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: 332
              Relay_Log_Space: 407
              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

  • 需要保证上面执行的过程中没有错误,并且Slave_IO_Running: Yes,Slave_SQL_Running: Yes,Slave_IO_State: Waiting for master to send event都是正常的,这样在进行master的数据写入或者修改的过程中,数据能够及时的进行同步
Master_Log_File: mysql-bin.000002  //主节点使用的二进制日志
Relay_Log_File: mysqld-relay-bin.000001 //表示中继日志
  • 接下来,对于使用二进制日志进行同步的方式,首先需要确定,slave上面存在对应的数据库,所以我们先创建数据库test这一点是需要注意的,进行数据库数据同步的前提是masterslave上面的数据库的库必须都是存在的
  • slave上面执行操作
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
  • master上面执行操作
mysql> use test;
Database changed
mysql> create table test ( username varchar(25), password varchar(30) );
Query OK, 0 rows affected (0.09 sec)
  • master上面的表结构
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@server7 ~]# 
  • slave上面的表结构
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> exit;
Bye
[root@server8 ~]# 
  • 接下来master上面插入数据
mysql> insert into test values('user1','123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('user2','234');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('user3','345');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
| user3    | 345      |
+----------+----------+
3 rows in set (0.00 sec)

mysql> exit;
Bye
  • 然后直接在slave上面查看这些数据:
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
| user3    | 345      |
+----------+----------+
3 rows in set (0.00 sec)

mysql> exit;
Bye
[root@server8 ~]# 
  • 上面是两个结点之间的主从复制模型,接下来再次增加一个节点server9.com
[root@server9 ~]# yum install mysql-server -y
  • 当存在三台主机时,就存在两种设计方案a--->b-->c或者a--->b a--->c
  • 接下来修改配置文件
[root@server9 ~]# vim /etc/my.cnf
添加
server-id=3
  • 然后重新启动服务器
[root@server9 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
  • 然后确保server9.com上面是存在test库的,但是这个库里面是没有任何的表的信息的;
  • 因为这个库里面的信息是空的,可以使用二进制日志来进行增量备份,或者使用mysldump来进行完整备份,这里使用二进制日志的方式来进行数据库在这里的恢复;
  • 查看二进制日志,找出正在时用的日志,并且找出插入table的位置

这里写图片描述

  • 在这之后还存在插入数据值的操作,找准合适的开始位置,和结束位置
[root@server9 ~]# mysqlbinlog --start-position=537 --stop-position=866 mysql-bin.000002 | mysql  <==这里指定的结束位置,表示在底下的内容之前就结束
  • 连接数据库查看数据是否已经同步
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> quit

  • 所以在查看上面的数据时,其实是少了一行的,关于位置的指定这是需要注意的地方,来弥补上面的错误,将866开始的日志重新执行一次
[root@server9 ~]# mysqlbinlog --start-position=866 mysql-bin.000002 | mysql 
  • 然后在进行查看

Database changed
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
| user3    | 345      |
+----------+----------+
3 rows in set (0.00 sec)
  • 接下来需要在server8.com上面授权server9.com来获得日志
mysql> grant replication slave on *.* to linux@'172.25.23.9' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
  • 然后需要在server9.com上面测试连接正常
[root@server9 ~]# mysql -h 172.25.23.8 -ulinux -pwestos
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.1.71 Source distribution
  • 然后修改sever8.com上面打开中继功能
[root@server8 ~]# vim /etc/my.cnf 
添加
server-id=2
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
log-slave-updates
  • 并且需要重新加载服务
[root@server8 ~]# /etc/init.d/mysqld reload
  • 更改master,并且启动slave
mysql> change master to master_host='172.25.23.7',master_user='linux',master_password='westos',master_log_file='mysql-bin.000001', master_log_pos=106;
Query OK, 0 rows affected (0.37 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
  • 查看slave的状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.23.8
                  Master_User: linux
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  • 接下来在master上面执行数据的插入操作,然后观察后面的数据是否及时的进行了同步
mysql> insert into test value('user00','1111');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
| user3    | 345      |
| user00   | 1111     |
+----------+----------+
4 rows in set (0.00 sec)

mysql> exit;
Bye
[root@server7 ~]# 
  • server8.com上面的数据
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
| user3    | 345      |
| user00   | 1111     |
+----------+----------+
4 rows in set (0.00 sec)

mysql> exit;
Bye
[root@server8 mysql]# 
  • 最后在server9.com上面的数据
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 234      |
| user3    | 345      |
| user00   | 1111     |
+----------+----------+
4 rows in set (0.00 sec)

mysql> exit;
Bye
[root@server9 ~]# 
  • 接下来需要一些软件包
  • 首先删除已经安装的软件包
[root@server7 mysql]# rpm -e --nodeps mysql.x86_64 mysql-libs.x86_64 mysql-server.x86_64

  • 然后安装新的软件包
[root@server7 mysql]# yum install -y mysql-community-libs-5.7.11-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm mysql-community-server-5.7.11-1.el6.x86_64.rpm mysql-community-client-5.7.11-1.el6.x86_64.rpm mysql-community-common-5.7.11-1.el6.x86_64.rpm
[root@server7 mysql]# /etc/init.d/mysqld start
  • 接下来需要查找临时安装的密码
2018-03-27T07:18:35.692015Z 1 [Note] A temporary password is generated for root@localhost: -WA&wc_L%3&m
  • 使用临时密码登陆,可能需要对于特殊字符进行转义
  • 对于这个密码进行转义然后进行登陆
-WA&wc_L%3&m

[root@server7 mysql]# mysql -p-WA\&wc_L%3\&m
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.11
  • 登陆之后,必须首先修改密码然后才能够进行操作
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
  • 执行mysql安全安装脚本执行安装
[root@server7 mysql]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root:   <==需要输入之前的密码,才能够进行修改;,密码必须符合安全性要求
  • 然后需要编辑配置文件,添加主从节点的配置信息
[root@server7 mysql]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog-do-db=test
  • 之后重启mysqld服务
[root@server7 mysql]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

  • 接下来还需要一个主机server9.com安装和server8.com以上同样的服务
  • 首先安装需要的软件包
[root@server9 mysql]# yum install -y mysql-community-libs-5.7.11-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm mysql-community-server-5.7.11-1.el6.x86_64.rpm mysql-community-client-5.7.11-1.el6.x86_64.rpm mysql-community-common-5.7.11-1.el6.x86_64.rpm
  • 然后删除上一次的mysql运行目录
[root@server9 mysql]# rm -fr /var/lib/mysql/
  • 启动新安装的mysql服务
[root@server9 mysql]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
  • 然后需要找到临时的密码,
[root@server9 mysql]# cat /var/log/mysqld.log | grep password | less

2018-03-27T07:52:31.306667Z 1 [Note] A temporary password is generated for root@localhost: oe:f66C.%yz8  <<===这个是密码
2018-03-27T07:55:05.853151Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.48TVj1.sql' started.
2018-03-27T07:55:06.649276Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.48TVj1.sql' ended.
2018-03-27T07:55:09.713243Z 0 [Note] Shutting down plugin 'sha256_password'
2018-03-27T07:55:09.713253Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-03-27T07:55:18.039981Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
2018-03-27T07:57:30.267076Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
  • 然后同样的需要修改密码
[root@server9 mysql]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)
[root@server9 mysql]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值