mysql安装及读写分离配置
配置信息
服务器配置说明
主机名 | ip地址 | 端口 |
---|---|---|
mysql_1 | 192.168.236.131 | 3306 |
mysql_2 | 192.168.236.132 | 3306 |
mysql_3 | 192.168.236.133 | 3306 |
软件版本:
mysql57-community-release-el7-10.noarch.rpm
一、安装(在线)
在CentOS中默认安装有MariaDB,这个是MySQL的分支,但为了需要,还是要在系统中安装MySQL,而且安装完成之后可以直接覆盖掉MariaDB。
1、下载并安装MySQL官方的 Yum Repository
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
2、安装
yum -y install mysql57-community-release-el7-10.noarch.rpm
3、覆盖掉之前的mariadb
yum -y install mysql-community-server
4、启动mysql服务
systemctl start mysqld
5、设置开机启动mysql
systemctl enable mysqld
systemctl daemon-reload
6、修改root本地登录密码
1|- 查看root账户的临时密码
cat /var/log/mysqld.log |grep password
2|- 登录mysql
mysql -u root -p
输入临时密码。
功登录mysql,修改密码。
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Pwd@1234';
7、查看密码策略规则
修改密码策略要求(默认密码策略要求密码是大小写字母+数字+特殊字母的组合而且最少8位,不需要修改策略的可以跳过这步):
SHOW VARIABLES LIKE 'validate_password%';
设置密码规则:
set global validate_password.check_user_name=OFF;
set global validate_password_policy=LOW;
set global validate_password_length=4;
flush privileges;
修改密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
8、关闭启动命令
1|- 关闭服务命令 service mysqld stop
2|- 启动服务命令 service mysqld start
二、设置远程访问
1、设置允许远程登录
use mysql;
update user set Host='%' where User='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
2、更改密码加密方式(可以远程用navicat连接)
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
3、关闭防火墙(不关闭不能远程访问),适用centos7*
1|- 查看防火墙状态
firewall-cmd --state
2|- 停止firewall
systemctl stop firewalld.service
3|- 禁止firewall开机启动
systemctl disable firewalld.service
三、数据配置
1、忽略大小写
linux下mysql安装完后是默认:区分表名的大小写,不区分列名的大小写,用root帐号登录后,
在/etc/my.cnf 中的 [mysqld]后添加添加 lower_case_table_names=1
重启MYSQL服务,这时已设置成功:不区分表名的大小写;
其中 0:区分大小写,1:不区分大小写 。
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:
1|- 数据库名与表名是严格区分大小写的;
2|- 表的别名是严格区分大小写的;
3|- 列名与列的别名在所有的情况下均是忽略大小写的;
4|- 变量名也是严格区分大小写的。
四、mysql主备
1、环境准备,两台服务器
--------------------------------------------------------
| 主机(master-1): IP地址: 192.168.236.131
| 主机(master-2): IP地址: 192.168.236.132
--------------------------------------------------------
2、修改配置文件
1|-master-1服务器中文件/etc/my.cnf 中添加如下配置:
[mysqld]
server-id=1 #server的唯一标识
auto_increment_offset=1 #自增id起始值
auto_increment_increment=2 #每次自增数字
log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
max_binlog_size=1024M #binlog单文件最大值
replicate-ignore-db = mysql #忽略不同步主从的数据库
replicate-ignore-db = information_schema
port=3306
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
user=mysql
max_connections=151
connection-control-failed-connections-threshold=5 #登陆失败次数限制
connection-control-min-connection-delay=108000 #限制重试时间,此处为毫秒,注意按需求换算
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 设置忽略大小写
lower_case_table_names = 1
# 指定编码
character-set-server=utf8
collation-server=utf8_general_ci
# 开启ip绑定
bind-address = 0.0.0.0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#指定客户端连接mysql时的socket通信文件路径
[client]
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8
2|-master-2服务器中文件/etc/my.cnf 中添加如下配置:
[mysqld]
server-id = 2 #server的唯一标识
auto_increment_offset = 2 #自增id起始值
auto_increment_increment = 2 #每次自增数字
log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
max_binlog_size=1024M #binlog单文件最大值
replicate-ignore-db = mysql #忽略不同步主从的数据库
replicate-ignore-db = information_schema
port=3306
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
user=mysql
max_connections=151
connection-control-failed-connections-threshold=5 #登陆失败次数限制
connection-control-min-connection-delay=108000 #限制重试时间,此处为毫秒,注意按需求换算
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 设置忽略大小写
lower_case_table_names = 1
# 指定编码
character-set-server=utf8
collation-server=utf8_general_ci
# 开启ip绑定
bind-address = 0.0.0.0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#指定客户端连接mysql时的socket通信文件路径
[client]
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8
3、配置重启mysql
service mysql restart(两台)
4、主从配置
1) 添加主从同步账户
在master-1中添加repl用户:grant replication slave on *.* to 'repl'@'%' identified by '123456';
在master-2中添加repl用户:grant replication slave on *.* to 'repl'@'%' identified by '123456';
5、查看主库的状态
在master-1上执行如下命令:
show master status
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | | | |
+------------------+----------+--------------+------------------+-------------------+
在master-2上执行如下命令:
show master status
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | | | |
+------------------+----------+--------------+------------------+-------------------+
6、配置同步数据
在master-1上执行如下命令:
change master to master_host='192.168.236.132',master_port=3306,master_user='repl',master_password='123456';
启动重名令
start slave;
7、问题解决
可能启动错误
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
错误原因:/var/lib/mysql/auto.cnf中的server-uuid一样,
解决方法:停止服务service mysqld stop 删除auto.cnf文件mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak,重启服务service mysqld start
原理分析:搭建集群是先在虚拟机上搭好主库环境,然后直接克隆镜像当作从库,这样会导致主从库生成相同的server-uuid。只需删除一个server-uuid并重启数据库服务即可解决问题
在正式环境使用时发现
Mysql创建函数报错This function has none of DETERMINISTIC, NO SQL, 的解决方案有一下三种:
a、set global log_bin_trust_function_creators = 1;
b、系统启动时 --log-bin-trust-function-creators=1
c、在my.ini(linux下为my.conf)文件中 [mysqld] 标记后加一行内容为 log-bin-trust-function-creators=1
多个主机有相同的server_uuid/server_id
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000001' at 2359, the last event read from './mysql-bin.000001' at 123, the last byte read from './mysql-bin.000001' at 2359.'
解决方法:请删除auto.cnf文件(auto.cnf文件在/etc/my.cnf中datadir配置的目录下),有时候启动从节点失败也可能和auto.cnf文件有关,删除auto.cnf文件重新启动slave服务。