Mysql集群搭建1-linux上Mysql的安装与配置

柔情只为你懂 2022-05-25 12:52 131阅读 0赞

#  #

# 1.Mysql集群的原理 #

![20180515103940933][]

通过访问虚拟ip值，在主keepalived没有宕机的情况下访问主keepalived的值对应的mysql,主keepalived宕机的情况下访问从keepalived对应的mysql.两个服务器通过虚拟ip关联.

# 2.Linux上Mysql的安装 #

#  #

**1、配置YUM源**

1.yum源配置

yum install -y [https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm][https_dev.mysql.com_get_mysql57-community-release-el7-11.noarch.rpm]

查看安装yum源成功

yum repolist enabled | grep "mysql.\*-community.\*"

![20180515104317816][]

可以修改 vim /etc/yum.repos.d/mysql-community.repo 源，改变默认安装的mysql版本。比如要安装5.6版本，将5.7源的enabled=1改 成        enabled=0。然后再将5.6源的enabled=0改成enabled=1即可。改完之后的效果如下所示：

![2018051510434153][]

**2、安装MySQL**

shell> yum install mysql-community-server

**3、启动MySQL服务**

shell> systemctl start mysqld

查看MySQL的启动状态

shell> systemctl status mysqld● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since 五 2016-06-24 04:37:37 CST; 35min ago Main PID: 2888 (mysqld) CGroup: /system.slice/mysqld.service └─2888 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

6月 24 04:37:36 localhost.localdomain systemd\[1\]: Starting MySQL Server... ----启动成功

6月 24 04:37:37 localhost.localdomain systemd\[1\]: Started MySQL Server. ----启动成功

**4、修改root本地登录密码**

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql进行修改：

shell> grep 'temporary password' /var/log/mysqld.log

shell> mysql -uroot -p默认密码mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

修改配置文件exit

vi / etc/my.cnf

datadir=/home/mysql/mysqldate

socket=/var/lib/mysql/mysql.sock

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

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

slow\_query\_log = on

slow-query-log-file = /var/log/slow.log

long\_query\_time=5

skip-name-resolve

innodb\_file\_per\_table = 1

innodb\_buffer\_pool\_size = 10G

innodb\_log\_file\_size = 256M

max\_connections = 2000

//mysql服务的id，不可重复

server\_id = 2

log-bin = mysql-bin

binlog\_format = mixed

read-only = 0

relay\_log = /home/mysql/mysqlbinlog/mysql-relay-bin 作为同步作用的文件夹，必须手动创建，修改权限

log-slave-updates = on

auto-increment-offset=2 序列自增规则必须一个奇数一个偶数

auto-increment-increment=2 序列自增规则必须一个奇数一个偶数

4、添加同步账号

添加主从同步账户:

mysql >  grant  replication  slave  on  \* . \*  to  'repl' @ '192.168.10.12'  identified  by  '123456' ;

mysql >  flush  privileges ;

查看主库的状态：

mysql >  show master status;

配置同步信息:

mysql >  change master  to  master\_host = '192.168.10.12' ,master\_port = **3306** ,master\_user = 'repl' ,master\_password = '123456' ,master\_log\_file = 'mysql-bin.000003' ,master\_log\_pos = **437** ;

查看主从配置信息:

mysql >  start slave;mysql >  show slave status\\G;

显示如下状态则正常:

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

为了避免出错可以将两个数据库 的密码设置的一致

# 出现的错误: #

1.1045 错误码

1.找不到服务 在配置文件内添加 skip-name-resolve 以此跳过域名和DNS解析

错误原因:

2.1236 错误码

Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000011' at 424, the last event read from './mysql-bin.000011' at 4, the last byte read f

解决办法：

在主库中查找：

/usr/local/mysql/bin/mysqlbinlog --start-position=144001931 /data/mysql/mysql-bin.001501

/\*!40010 SET @@session.max\_insert\_delayed\_threads=0\*/;

/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;

DELIMITER /\*!\*/;

\# at 40019  --真正的开始位置

\#130629 12:00:50 server id 2  end\_log\_pos 107  Start: binlog v 4, server v 5.5.15-log created 130629 12:00:50

\# Warning: this binlog is either in use or was not closed properly.

BINLOG '

8lvOUQ8CAAAAZwAAAGsAAAABAAQANS41LjE1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/\*!\*/;

DELIMITER ;

\# End of log file

ROLLBACK /\* added by mysqlbinlog \*/;

/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;

然后重新

change master to master\_host='192.168.50.35',master\_port=3306,master\_user='repl',master\_password='trsadmin123',master\_log\_file='mysql-bin.000011',master\_log\_pos=40019;

必须先reset slave;

[20180515103940933]: /images/20220525/2f154f584c5f4b48865181e39190d74c.png
[https_dev.mysql.com_get_mysql57-community-release-el7-11.noarch.rpm]: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[20180515104317816]: /images/20220525/61539341a3534941a1070ccdd00a15bd.png
[2018051510434153]: /images/20220525/68a2948d0d46489b8f7d8ce26e4fb620.png