mysql主从复制

linux安装mariadb

虚拟机安装linux请看https://blog.csdn.net/weixin_44102152/article/details/109177863
安装

# 安装
yum install -y mariadb-server
#启动
systemctl start mariadb.service
#设置账号密码
/usr/bin/mysqladmin -u root password "root"
#重启
systemctl restart mariadb.service
#登录
mysql -u root -p
#登录后开启远程访问
grant all privileges on *.* to 'root' @'%' identified by 'root';
#刷新权限
flush privileges;
#mysql服务随系统启动
systemctl enable mariadb.service
#关闭防火墙
systemctl stop firewalld.service
#禁止防火墙随系统启动
systemctl disable firewalld.service

主从复制

我这边的配置 mastar 192.168.218.129
slave 192.168.218.132

主机配置
修改配置文件：vi /etc/my.cnf

#主服务器唯一ID
service-id=1
#启动二进制日志 master-bin
log-bin=mysql-bin
#设置不要复制的数据库（可设置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库 （如果两个都配置，那么已这个为准）
binlog-do-db = 需要复制的主数据库的名字
#设置logbin格式
binlog_format=STATEMENT

从机配置

修改配置文件： vi /etc/my.cnf

#从服务器唯一id
server-id=2
#启动中继日志
relay-log=mysql-relay

主机、从机重启MySQL服务

systemctl restart mariadb.service

主机、从机都关闭防火墙

systemctl stop firewalld.service

在主机上建立账户并授权slave

#在主机登录mysql里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查询master的状态
show master status;

这里的话可能会遇到一个问题
show master status; 查询出来是空，my.cnf没配置好
在

[root@localhost ~]# cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
[root@localhost ~]# systemctl restart mariadb.service

启动失效可能原因 log-bin=mysql-bin

从服务器配置
登录mysql去配置,注意mysql.bin.000001和154

CHANGE MASTER TO MASTER_HOST='192.168.218.129',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql.bin.000001',MASTER_LOG_POS=154;

启动

start slave;

查看从服务器状态

show slave status\G;
#看到这两个yes就ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

补充 停止：stop slave; reset master;

卸载mariadb（安装mysql前准备工作）

#先查询
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
#卸载
[root@localhost ~]# yum remove mariadb-libs-5.5.68-1.el7.x86_64
#输入y继续

删除配置文件数据目录

rm -f /etc/my.cnf
rm -rf /var/lib/mysql/

查找所有mysql对应的文件夹 并删除

[root@localhost ~]# whereis mysql
mysql: /usr/lib64/mysql
[root@localhost ~]# rm -rf /usr/lib64/mysql
[root@localhost ~]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
[root@localhost ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql

创建用户组–检查mysql用户组和用户是否存在，如果没有，则创建

[root@localhost /]# cat /etc/group | grep mysql
[root@localhost /]# cat /etc/passwd |grep mysql
[root@localhost /]# groupadd mysql
[root@localhost /]# useradd -r -g mysql mysql
[root@localhost /]# 

安装mysql

1、先安装wget

yum -y install wget

2、安装mysql5.7，如果提示证书错误 加上 --no-check-certificate

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

3、wget的下载目录解压

tar xzvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

4、转移

mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql

5、在/usr/local/mysql目录下创建data目录

mkdir /usr/local/mysql/data

6、更改用户组和权限

[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql
[root@localhost ~]# chmod -R 755 /usr/local/mysql

7、编译安装并初始化mysql，务必记住初始化输出日志末尾的密码（数据库管理员临时密码）

[root@localhost ~]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
2021-04-05T12:13:11.016395Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-05T12:13:11.646688Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-05T12:13:11.863985Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-05T12:13:11.946823Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4a8b1d24-9608-11eb-86df-000c2942d2af.
2021-04-05T12:13:11.948875Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-04-05T12:13:11.950051Z 1 [Note] A temporary password is generated for root@localhost: 4Sg!kOANg<E;

129 4Sg!kOANg<E;
132 yqM?E1#Jsrao

补充：
如果出错执行下面这两句，再重复编译

yum install  libaio-devel.x86_64
yum -y install numactl

8、编辑配置文件my.cnf，添加配置如下

[root@localhost bin]#  vi /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=600
innodb_file_per_table=1
lower_case_table_names=1
character_set_server=utf8

lower_case_table_names：是否区分大小写，1表示存储时表名为小写，操作时不区分大小写；0表示区分大小写；不能动态设置，修改后，必须重启才能生效：
character_set_server：设置数据库默认字符集，如果不设置默认为latin1
innodb_file_per_table：是否将每个表的数据单独存储，1表示单独存储；0表示关闭独立表空间，可以通过查看数据目录，查看文件结构的区别；

9、测试启动mysql服务器

/usr/local/mysql/support-files/mysql.server start

异常情况
#查询服务
ps -ef|grep mysql | grep -v grep
ps -ef|grep mysqld | grep -v grep
#结束进程
kill -9 PID
#启动服务
/usr/local/mysql/support-files/mysql.server start

10、添加软连接，并重启mysql服务

[root@localhost bin]# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@localhost bin]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@localhost bin]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

11、登录mysql，修改密码(密码为步骤7生成的临时密码)

[root@localhost bin]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password for root@localhost = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)

12、开放远程连接

use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set user.Host='%' where user.User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

13、设置开启自启动

#1、将服务文件拷贝到init.d下，并重命名为mysql
[root@localhost bin]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
#2、赋予可执行权限
[root@localhost bin]# chmod +x /etc/init.d/mysqld
#3、添加服务
[root@localhost bin]# chkconfig --add mysqld
#4、显示服务列表
[root@localhost bin]# chkconfig --list