vlambda博客
学习文章列表

wget -c https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

rpm -qa|grep mariadbrpm -qa|grep mariadb*

yum remove mariadbyum remove mariadb-libs

rm -f /etc/my.cnf

rm -rf /var/lib/mysql/

# 查看libaio、perl，net-tools有什么版本yum list | grep libaio （perl，net-tools）yum install -y libaio.x86_64

sudo rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm# 如果报错 + 上 “--nodeps”# 原因：这是由于yum安装了旧版本的GPG keys造成的 解决办法：后面加上 --force --nodeps sudo rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm --nodeps

rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm --nodeps

# 数据库数据存储目录mkdir -p /data/mysql# 数据库日志存储目录mkdir -p /data/mysql/log

groupadd mysql

 useradd -r -s /sbin/nologin -g mysql mysql -d /usr/bin/mysql

mkdir -p /data/mysql/datamkdir -p /data/mysql/logmkdir -p /data/mysql/binlogmkdir -p /data/mysql/share
# 变更权限chown -R mysql:mysql /data/mysql/datachown -R mysql:mysql /data/mysql/logchown -R mysql:mysql /data/mysql/binlogchown -R mysql:mysql /data/mysql/share

vim /etc/my.cnf

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysql]# 默认的数据库字符集default-character-set=utf8mb4
# 错误日志文件messages = en_USlc-messages-dir = /data/mysql/share## 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
# 事物引擎default-storage-engine=INNODB
# 默认字符集character_set_server=utf8mb4
# 时间戳配置（解决初始化错误问题）explicit_defaults_for_timestamp=true
# 错误日志文件lc-messages = en_USlc-messages-dir = /data/mysql/share
# 单独设置binlog 日志路径，最好与数据目录分开，设置到不同磁盘分区，这样提高IO效率log-bin = /data/mysql/binlog/mysql-bin.log
 # 服务器唯一标识，启动binlog日志就必须设置唯一id ，否则无法启动 server-id=1  log_bin_index=/data/mysql/binlog/mysql-bin.index
 # 主从同步采用此种格式的binlog binlog-format=MIXED
# binlog 优化配置binlog_cache_size = 1Mmax_binlog_cache_size = 128Mmax_binlog_size = 1G
# 连接池优化max_connections = 512# 表文件描述符的缓存大小优化table_open_cache = 2048
# 链接池首次分配内存大小sort_buffer_size = 1M# 语句内关联分配内存大小join_buffer_size = 2M
# 查询结果传输最大值优化max_allowed_packet = 128M

datadir=/data/mysql/databasedir=/usr/sbin
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0
log-error=/data/mysql/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

[mysqld_safe]# 增加错误日志，当数据库无法启动等问题时会产生错误日志，通过日志来排查问题log-error = /data/mysql/log/error.log

mysqld --initialize --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/sbin --datadir=/data/mysql/data

mysql_ssl_rsa_setup --datadir=/data/mysql/data

vim /etc/my.cnf
# id 设置为内网地址最后一段server-id=28log_bin=master-binlog_bin_index=master-bin.indexbinlog_do_db=testbinlog_do_db = mysql#备注：#server-id 服务器唯一标识。#log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可

# 查看启动命令Listsystemctl list-unit-files --type=service | grep mysql# 运行命令systemctl start mysqld.service

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
# 查看 log 日志，对应处理cat /data/mysql/log/mysqld.log

mysqladmin -uroot -p密码 password 新密码

# 配置文件中添加：skip-grant-tables# 重启mysqlsystemctl restart mysqld.service# 不需要密码可以直接登陆mysql -uroot -p

# IP地址，写 backup节点的IP地址。重点是前面的 replication slavegrant replication slave on *.* to masterbackup@'192.168.x.%' identified by '123456';
#备注#192.168.17.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP#若将 192.168.17.% 改为 %，则任何ip均可作为其从数据库来访问主服务器
退出mysqlmysql> exit;service mysql restartmysql -uroot -p show master status;　　　　　　　　　　　　　　#记录pos 位置，和日志文件名字

截止到常规配置（过程略....）

# 从机 01vim /etc/my.cnf
# id 设置为内网地址最后一段server-id=1# slave只读限制read-only =1
binlog_do_db = testbinlog_do_db = mysqlrelay-log=slave-relay-birelay-log-index=slave-relay-bin.index

# 从机 27vim /etc/my.cnf
# id 设置为内网地址最后一段server-id=27# slave只读限制read-only =1
binlog_do_db = testbinlog_do_db = mysqlrelay-log=slave-relay-birelay-log-index=slave-relay-bin.index

systemctl start mysqld.service

# 链接 slave 01、27[密码去初始化的日志里找]mysql -uroot -p# 初始化密码SET PASSWORD = PASSWORD('新密码');
# 停止从机同步stop slave;# 注意 ：master_host，master_port，master_user# 注意2: master_log_file 跟 master_log_pos 跟 master 上的截图一致；【主机上 show master status;】change master to master_host='192.168.1.1',master_port=88306,master_user='masterbackup',master_password='123456',master_log_file='master-bin.000004',master_log_pos=154;
# 启动从机同步start slave
show slave status\G;　　　　　　　　#查看状态是否正常
# 备注：# master_log_file，master_log_pos 为在主上执行show master status命令查看获得信息# show slave status;\G　　 　　查看状态如下全为yes 即可，否则配置有问题# Slave_SQL_Running: Yes# Slave_IO_Running: Yes

# 普通用户所有权限grant all privileges  on *.*  to "用户"@'网段%' identified by '密码';# 收回super权限revoke super on *。* from '用户'@"%";

# root 用户登陆mysql -uroot -p# 配置数据同步用户grant replication slave on *.* to masterbackup@'192.168.x.%' identified by '123456';

# 下载 maxscalewget https://downloads.mariadb.com/MaxScale/1.4.5-debug/centos/7server/x86_64/maxscale-1.4.5-1.centos.7.x86_64.rpm# 安装rpm -ivh maxscale-1.4.5-1.centos.7.x86_64.rpm

create user scalemon@’192.168.0.%’ identified by ‘123456’;grant replication slave, replication client on *.* to scalemon@’192.168.0.%’;

create user maxscale@’192.168.0.%’ identified by ‘123456’;grant select on mysql.* to maxscale@’192.168.0.%’ ;

vim /etc/maxscale.cnf

# MaxScale documentation on GitHub:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
# Global parameters## Complete list of configuration options:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]threads=1
# Server definitions## Set the address of the server to the network# address of a MySQL server.#
[server1]type=serveraddress=192.168.0.xxport=3306protocol=MySQLBackend

[server2]type=serveraddress=192.168.0.xxport=3306protocol=MySQLBackend

[server3]type=serveraddress=192.168.0.xxport=3306protocol=MySQLBackend

# Monitor for the servers## This will keep MaxScale aware of the state of the servers.# MySQL Monitor documentation:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md
[MySQL-Monitor]type=monitormodule=mysqlmonservers=server1,server2,server3user=监控passwd=监控密码monitor_interval=10000detect_stale_master=true# Service definitions## Service Definition for a read-only service and# a read/write splitting service.#
# ReadConnRoute documentation:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
# [Read-Only Service]# type=service# router=readconnroute# servers=server1# user=myuser# passwd=mypwd# router_options=slave
# ReadWriteSplit documentation:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
[Read-Write-Service]# type=service type=servicerouter=readwritesplitservers=server1,server2,server3user=路由 passwd=路由密码max_slave_connections=100%max_slave_replication_lag=60use_sql_variables_in=all
# This service enables the use of the MaxAdmin interface# MaxScale administration guide:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
[MaxAdmin-Service]type=servicerouter=cli
# Listener definitions for the services## These listeners represent the ports the# services will listen on.#
# [Read-Only Listener]# type=listener# service=Read-Only Service# protocol=MySQLClient# port=4008
[Read-Write-Listener]type=listenerservice=Read-Write-Serviceprotocol=MySQLClientport=7306
[MaxAdmin-Listener]type=listenerservice=MaxAdmin-Serviceprotocol=maxscaledport=7305socket=default

maxscale --config=/etc/maxscale.cnf

#默认用户名密码 admin；mariadbmaxadmin --user=admin --password=mariadb -P端口号

MaxScale> list servers

Select @@hostname;start transaction;Select @@hostname;roolback;Select @@hostname;