centos 7 安装mysql 搭建主从复制

最新推荐文章于 2024-08-18 17:37:54 发布

xiaoweichihuo

最新推荐文章于 2024-08-18 17:37:54 发布

阅读量135

点赞数

分类专栏： mysql 文章标签： mysql 主从搭建

本文链接：https://blog.csdn.net/xiaoweichihuo/article/details/94378630

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Centos 7 安装 mysql-5.7.21-el7

关闭防火墙
检查防火墙状态

systemctl status firewalld.service

firewall-cmd --state

关闭防火墙

# systemctl stop firewalld.service
# systemctl disable firewalld.service

第一条命令是立马关闭防火墙，但重启系统后防火墙又自动处于运行状态。
第二条命令是永远关闭防火墙，重启也是禁止的。

# firewall-cmd --state
not running

关闭selinux
检查selinux状态

getenforce

修改/etc/selinux/config 文件，将SELINUX=enforcing改为SELINUX=disabled或

SELINUX=permissive
# vi /etc/selinux/config
……
SELINUX=disabled
……

保存后退出，需要重启系统生效

# reboot

重启后确认

3 删除centos7 默认安装的MariaDB数据库

    # rpm -qa | grep maria

mariadb-libs-5.5.50-1.el7_2.x86_64

# yum remove mariadb-libs –y
或者
# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64

4．查看linux 系统版本和内核版本

cat /etc/redhat-release     查看操作系统版本
uname -r               查看系统内核版本

下载对应的mysql版本

安装相关的依赖包

yum install -y  gcc gcc-c++ ncurses ncurses-devel bison libgcrypt perl make

检查是否安装过mysql

rpm -qa | grep mysql

5 新建mysql用户和用户组创建相关目录

  groupadd  mysql
  useradd  -g mysql mysql
  mkdir -p /data/mysql
  mkdir -p /data/log/mysql
  chown -R mysql:mysql /data/mysql
  chown -R mysql:mysql /data/log/mysql

目录规划：
/var/lib/mysql/ #数据库目录
/usr/share/mysql #配置文件目录
/usr/bin #相关命令目录
/etc/init.d/mysql #启动脚本
/data/mysql 数据文件目录
/data/log 日志文件目录

6.上传tar包到linux系统

 创建密码： passwd mysql       Mysql@2019

本次上传到 /home/mysql
mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar
解压
tar -xvf mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar

7 安装
rpm安装mysql 按顺序安装
rpm -ivh mysql-community-common-5.7.21-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.21-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.21-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm

说明：
server mysql服务端最后安装
devel 所需的库和包含文件。如果你想要编译其他MySQL客户程序, 例如Perl模块
client mysql客户端

8修改配置文件位置
修改安装目录的属组为mysql

chown -R mysql:mysql /var/lib/mysql/

修改配置文件内容

[mysqld]
basedir = /var/lib/mysql
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
character_set_server=utf8

# 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
lower_case_table_names=1

general_log = 1
#skip-name-resolve
#skip-networking
back_log = 300
max_connections=1000

[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

加入下面两个参数，一个是sql错误消息提示文件地址，一个是告警日志时间设置为与系统一致

lc-messages-dir=/usr/share/mysql/english/errmsg.sys
log_timestamps=SYSTEM

初始化数据库

/usr/sbin/mysqld --initialize --user=mysql --basedir=/var/lib/mysql --datadir=/data/mysql

设置mysql服务开机启动

systemctl start mysqld
systemctl enable mysqld
systemctl is-enabled mysqld

查看默认root密码

cat  /var/log/mysqld.log   
[Note] A temporary password is generated for root@localhost: B(5-OgW(iO7n

登录修改密码

mysql -uroot -p"B(5-OgW(iO7n"

SET PASSWORD = PASSWORD('密码');
Exit

登录确认

mysql -uroot -p"密码"

不知道root用户密码修改密码方法
在/etc/my.cnf中添加skip-grant-tables
重启mysql服务
systemctl restart mysqld
重新登录mysql
mysql
use mysql
update user set authentication_string=password(“你的新密码”) where user=“root”;
flush privileges;
quit
在去掉/etc/my.cnf中skip-grant-tables
重启mysql
用新密码登录

安装完成

主从复制搭建

使用gtid方式同步
主库my.cnf配置增加

[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=166
report-port=3306
report-host=10.20.8.166

主库配置文件：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# 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
basedir = /var/lib/mysql
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
character_set_server=utf8

# 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
lower_case_table_names=1

open_files_limit = 65535
table_open_cache=2010


general_log = 1
skip-name-resolve
#skip-networking
back_log = 300
max_connections=1000

binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=166
report-port=3306
report-host=10.20.8.166


[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

备库增加参数

binlog-format=ROW
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=167
report-port=3306
log-bin=mysql-bin.log
report-host=10.20.8.167

备库详细配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# 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
basedir = /var/lib/mysql
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
character-set-server = utf8
lower_case_table_names=1  

# 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

general_log = 1
skip-name-resolve
#skip-networking
back_log = 300
max_connections=1000

binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=167
report-port=3306
log-bin=mysql-bin.log
report-host=10.20.8.167

[client]
default-character-set=utf8 
socket=/var/lib/mysql/mysql.sock

主库创建同步用户

GRANT REPLICATION SLAVE ON *.* TO repluser@10.20.8.167 IDENTIFIED BY 'replpass';
flush privileges;

从服务器执行同步操作

CHANGE MASTER TO MASTER_HOST='10.20.8.166', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;

start slave;

查看主备库状态

show slave status\G;
show master status\G;

如果没启用GTID，需要使用如下命令：
CHANGE MASTER TO MASTER_HOST=‘172.16.100.6’,
-> MASTER_USER=‘repluser’,
-> MASTER_PASSWORD=‘replpass’,
-> MASTER_LOG_FILE=‘master-bin.000003’,
-> MASTER_LOG_POS=1174;

创建远程连接用户

GRANT ALL PRIVILEGES ON *.* TO 用户名@"%" IDENTIFIED BY '密码' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 用户名@"%" IDENTIFIED BY '密码' WITH GRANT OPTION;

修改用户连接方式

use mysql;
update user set host = '%' where user = 'whf'; （如果写成 host=localhost 那此用户就不具有远程访问权限）
flush privileges;
grant all privileges on *.* to 'myuser'@'%' identified by 'mypassword' with grant option;

grant all privileges on *.* to 创建的用户名 @"%" identified by "密码";

flush privileges;

create table tt(id int, name varchar(10));