最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

Linux下Mysql集群使用

XAMPP教程 中文小张 824浏览 0评论

1.Linux安装,YUM,网络设置,略。

2.查看linux版本:
[root@localhost ~]# uname -a
Linux localhost.localdomain 3.10.0-957.el7.x86_64 #1 SMP Thu Oct 4 20:48:51 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

检查是否安装了Mariadb,避免和Mysql冲突:
[root@localhost ~]# yum list installed | grep mariadb
mariadb-libs.x86_64    1:5.5.60-1.el7_5      @anaconda/7.6

以上结果说明有自带的Mariadb,卸载:

[root@localhost ~]# yum -y remove mariadb*

3.下载Mysql安装包(这里是方案一,直接下载bundle包,避免包依赖问题):

4.进入到rpm包所在目录,先解压tar包,结果:

[root@localhost mysql]# tar -xvf mysql-8.0.17-1.el7.x86_64.rpm-bundle.tar
mysql-community-test-8.0.17-1.el7.x86_64.rpm
mysql-community-server-8.0.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.17-1.el7.x86_64.rpm
mysql-community-client-8.0.17-1.el7.x86_64.rpm
mysql-community-devel-8.0.17-1.el7.x86_64.rpm
mysql-community-common-8.0.17-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.17-1.el7.x86_64.rpm
mysql-community-libs-8.0.17-1.el7.x86_64.rpm

再用安装命令:
[root@localhost mysql]# rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm

可能会提示错误:
error: Failed dependencies:
mysql-community-client(x86-64) >= 8.0.11 is needed by mysql-community-server-8.0.17-1.el7.x86_64
mysql-community-common(x86-64) = 8.0.17-1.el7 is needed by mysql-community-server-8.0.17-1.el7.x86_64

在此rpm文件目录,安装mysql,须按照如下安装顺序进行,另外:如果使用YUM安装也可以,请先配置好mysql的yum源,就能自动解决依赖问题,对应的安装方法见<后记>部分:

[root@localhost mysql]# rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm
[root@localhost mysql]# rpm -ivh mysql-community-libs-8.0.17-1.el7.x86_64.rpm
[root@localhost mysql]# rpm -ivh mysql-community-libs-compat-8.0.17-1.el7.x86_64.rpm
[root@localhost mysql]# rpm -ivh mysql-community-client-8.0.17-1.el7.x86_64.rpm
[root@localhost mysql]# rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm

5.启动mysql(停止为stop):
[root@localhost mysql]# systemctl start mysqld.service

6.登录:
[root@localhost mysql]# mysql -u root -p

7.如果提示密码必填或错误,/etc/my.cnf/中查看日志位置,
[root@localhost mysql]# ls /etc/my.cnf/

然后去日志中找到临时密码:

[root@localhost mysql]# more /var/log/mysqld.log

8.再次登录,修改密码并修改密码策略,当然可以不改,但为了方便,先修改下:
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘Abcd@1234’;
mysql> show variables like ‘validate%’;
mysql> set global validate_password.policy = 0;
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘abcd@1234’;

9.刷新下权限:
mysql> flush privileges;

10.为了试验方便,关闭防火墙:
[root@localhost biao]# systemctl stop firewalld.service
[root@localhost biao]# systemctl disable firewalld.service

11.建立另一个从机mysql实例,我直接copy为另一个虚拟机,并修改IP,此时两个 MySQL 实例的 uuid 一样(正常安装是不会相同的),这时需要手动修改,修改位置在 /var/lib/mysql/auto.cnf ,注意随便修改这里几个字符即可,但也不可太过于随意,例如修改了 uuid 的长度。

[root@localhost ~]# vim /var/lib/mysql/auto.cnf

12.核心语句:主实例配置(ip 192.168.1.204),创建一个用户,并授权所有表权限:
mysql> CREATE USER ‘rep01’@’192.168.1.205’ IDENTIFIED WITH mysql_native_password BY ‘abcd@1234’;
mysql>  GRANT REPLICATION SLAVE ON *.* TO ‘rep01’@’192.168.1.205′;

以上表示配置从机登录用户名为 rep01,密码为 abcd@1234,并且必须从 192.168.1.205这个地址登录,登录成功之后可以操作任意库中的任意表。其中,如果不需要限制登录地址,可以将 IP 地址更换为一个 % 号。

13.刷新表权限
mysql> flush privileges;

14.配置binarylog
[root@localhost ~]# vim /etc/my.cnf

设置server-id,[mysqld]后添加内容如下,注意每次修改后要重启mysql服务才会生效,

log-bin=/var/lib/mysql/binlog
server-id=111
binlog-do-db= test

说明:

log-bin同步的日志文件和路径,一定注意这个目录是mysql可以写入的;

binlog-do-db要同步的数据库名,当从机连上主机,只有这里的DB才会同步,多个可以用逗号隔开;

server-id主从环境下的唯一ID,可以任意数字,但集群中不能有重复;

15.完成后,重启mysql:
[root@localhost ~]# systemctl restart mysqld

16.查看主实例当前binarylog和偏移量,这样,从实例启动后,就从这个点开始数据恢复:
mysql> show master status;

至此,主实例配置完成!

17.从实例(ip 192.168.1.205)开始:同样修改/etc/my.cnf文件,[mysqld]后添加如下内容:

server-id=211

18.使用命令来配置主从关系:

mysql> change master to
-> master_host=’192.168.1.204′,
-> master_port=3306,
-> master_user=’rep01′,
-> master_password=’abcd@1234′,
-> master_log_file=’binlog.000002’,
-> master_log_pos=155;

这里配置了主实例地址,端口及从机登录的账号信息,特别注意最后两参数与主实例中一致!如果此时提示有实例在运行,可以先使用命令:stop slave;

19.启动slave进程:
mysql> start slave;

20.查看从实例状态:
mysql> show slave status\G;

状态示例:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.204
Master_User: rep01
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 155
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.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: 155
Relay_Log_Space: 531
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 111
Master_UUID: b67e3ee1-d94a-11e9-849a-000c29b3fc17
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
主要是:Slave_IO_Running: Yes / Slave_SQL_Running: Yes 都为yes,则表示配置正确!如果有一个不为yes,表示主从配置失败,可阅读日志,具体问题再具体解决。

21.测试下:
master:  创建database–>创建表–>写入数据

slave:     创建database–>查看表–>查询数据

发现主从数据一致,(注意从机的DB要手动创建),贴图略!从机再次查看状态:

mysql> show slave status\G;

有以下记录,即证明已经做了同步:


Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

22.如需扩展为一写多读,增加从机数量即可,还可多写多读,请君自建。

后记:

1.yum方法安装mysql,如果无法使用wget,先安装wget:yum install wget,下载rpm包(noarch–no architecture版本,架构无关版):

[root@localhost ~]#wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

执行安装命令:
[root@localhost ~]#rpm -ivh mysql80-community-release-el7-1.noarch.rpm

以上过程会自动安装好mysql的yum源,然后就可以使用yum安装了,检查yum源:

[root@localhost ~]#yum repolist enables | grep “mysql.*-community.*”

成功的结果示例如下:

yum方式安装mysql:

[root@localhost ~]#yum install mysql-server

然后就是启动/登录:
[root@localhost ~]#systemctl start mysqld
[root@localhost ~]#mysql -u root -p

2.停止msyql:
[root@localhost ~]# systemctl stop mysqld

3.说了这么多,好像读写分离都没提到,其实这个可以在应用端解决,update型接入Master,query类型接入Slave,做两个Datasource即可,也可以使用Mycat之类的DB中间件做读写分离配置。以上方案仅供参考!

转载请注明:XAMPP中文组官网 » Linux下Mysql集群使用

您必须 登录 才能发表评论!