• 欢迎访问显哥博客,本网站纯属学习技术,绝无商业用途,欢迎小伙伴们共同学习!研究技术!QQ:52249909 加我QQ
  • 世界75亿人,这么小的概率,能认识你,是我一生的幸运,不妨加个QQ接触一下:52249909 加我QQ

Centos7-MySQL 5.7的安装及主从复制【显哥出品,必为精品】

MySQL lixian 10个月前 (12-24) 1185次浏览 0个评论 扫描二维码
文章目录[隐藏]

环境准备

服务器IP 计算机名 角色
192.168.1.149 master 主库
192.168.1.142 slave 从库

MySQL安装部署(两个机器一样的操作)

1.下载,解压,并创建软链接

#wget下载mysql5.7软件包(如果没有wget命令要yum下载哦~)
[root@master ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
[root@master ~]# ls -lh
总用量 625M
-rw-r--r--. 1 root root 625M 3月  18 2017 mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

#解压到/usr/local目录下
[root@master ~]# tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@master ~]# cd /usr/local/

#在/usr/local目录下创建软连接
[root@master  /usr/local]# ln -sv mysql-5.7.18-linux-glibc2.5-x86_64/ mysql
"mysql" -> "mysql-5.7.18-linux-glibc2.5-x86_64/"

2.新建mysql用户组和mysql用户

[root@master /usr/local]# groupadd mysql
[root@master /usr/local]# useradd -g mysql -r -s /sbin/nologin -M -d /data/mysqldata mysql

3.新建数据目录并赋予相关属组及权限

[root@master /usr/local]# chown -R mysql:mysql /usr/local/mysql/
[root@master /usr/local]# mkdir -p /data/mysqldata
[root@master /usr/local]# chmod -R 770 /data/mysqldata
[root@master /usr/local]# chown -R mysql:mysql /data/mysqldata

4.初始化mysqld,生成mysql初始密码

[root@master /usr/local]# cd /usr/local/mysql

#初始化mysqld(会提示有个报错,缺少libaio插件)
[root@master /usr/local/mysql]# ./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata --initialize
./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

#安装libaio
[root@master /usr/local/mysql]# yum install -y libaio

#再次初始化mysqld,日志最下方输出mysql初始密码  (hS9Le#Ki9dM 
[root@master /usr/local/mysql]# ./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata --initialize
2020-12-24T07:39:53.161470Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-24T07:39:56.359351Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-24T07:39:56.836233Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-24T07:39:56.952342Z 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: 383afe3f-45bb-11eb-a3ec-ca28bacb4f17.
2020-12-24T07:39:56.976172Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-24T07:39:56.976892Z 1 [Note] A temporary password is generated for root@localhost: (hS9Le#Ki9dM

5.配置环境变量,使用systemctl来管理mysqld服务

注意:centos7使用systemctl,如果是centos6使用下面的扩展:service来管理mysqld服务

#配置环境变量
[root@master /usr/local/mysql]# echo "PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
[root@master /usr/local/mysql]# source /etc/profile.d/mysql.sh

#编写system文件,使用systemctl来管理mysqld服务
[root@master /usr/local/mysql]# vim /usr/lib/systemd/system/mysql.service
[Unit]
Description=MySQL Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
LimitNOFILE = 65535
Restart=on-failure
RestartSec=3
RestartPreventExitStatus=1
PrivateTmp=false

#重载systemctl配置
[root@master /usr/local/mysql]# systemctl daemon-reload

#配置my.cnf文件
[root@master /usr/local/mysql]# vim /etc/my.cnf
[mysqld]
user=mysql
port=3306
datadir=/data/mysqldata
basedir=/usr/local/mysql
plugin-dir=/usr/local/mysql/lib/plugin
socket=/tmp/mysql.sock
symbolic-links=0
#如果是salve从库,server_id的值要大于1 ,不能一样,反正比主库的大就行
server_id=1 
[mysqld_safe]
log-error=/data/mysqldata/master.err
pid-file=/data/mysqldata/master.pid
!includedir /etc/my.cnf.d

#启动MySQL服务
[root@master /usr/local/mysql]# systemctl start mysql.service 
[root@master /usr/local/mysql]# ps aux |grep mysql
mysql    10050  5.2  0.5 1190064 179824 ?      Sl   02:00   0:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize

systemctl enable mysql.service   ---设置开启自启动

systemctl start mysql.service      ----开启MySQL服务

systemctl stop mysql.service     -----关闭MySQL服务

systemctl restart mysql.service     -----重启MySQL服务

systemctl status mysql.service  -----查看MySQL服务状态

扩展:使用service来管理mysqld服务、配置环境变量,如果使用上面的systemctl就不用操作这一步了

[root@master /usr/local/mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@master /usr/local/mysql]# ldconfig
[root@master /usr/local/mysql]# echo "PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
[root@master /usr/local/mysql]# source /etc/profile.d/mysql.sh
[root@master /usr/local/mysql]# chkconfig mysqld on

6.登录mysql并修改密码

#如果密码有通配符,可以用单引号括起来,或者在交互模式粘贴
[root@master /usr/local/mysql]# mysql -uroot -p'(hS9Le#Ki9dM'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18

Copyright (c) 2000, 2017, 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='123456';
mysql> use mysql;
mysql> update user set host = '%' where user = 'root';
mysql> FLUSH PRIVILEGES;
mysql> exit
[root@master /usr/local/mysql]# mysql -uroot -p123456

主从复制

1.master上开启binlog日志,配置如下

[root@master ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysqldata
socket=/tmp/mysql.sock
user=mysql
server_id=1
port=3306
log-bin=mysql-bin
[root@master ~]# systemctl restart mysql.service

2.测试log_bin是否成功开启(ON表示开启)

[root@master ~]# mysql -uroot -p123456
mysql> show variables like '%log_bin%';

Centos7-MySQL 5.7的安装及主从复制【显哥出品,必为精品】

3.master的数据库中建立备份账号

backup为用户名,%表示任何远程地址,如下表示密码为1234的任何远程地址的backup都可以连接master主机

mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
mysql> use mysql
mysql> select user,authentication_string,host from user;
+-----------+-------------------------------------------+-----------+
| user      | authentication_string                     | host      |
+-----------+-------------------------------------------+-----------+
| root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
+-----------+-------------------------------------------+-----------+

4.master查看file 和position值

mysql> show master status\G 
*************************** 1. row ***************************
             File: mysql-bin.000007
         Position: 431
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

从库操作

#编辑配置文件,修改server_id值,要和主库的不一样
[root@slave ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysqldata
socket=/tmp/mysql.sock
user=mysql
server_id=2
port=3306
read_only = 1

#重启mysql服务,使配置生效
[root@slave ~]# systemctl restart mysql

#进入数据库
[root@slave ~]# mysql -uroot -p123456

#停止从库
mysql> stop slave;

#配置主库的信息(要和主库查找的file 和position值一样)
mysql> change master to
    -> master_host='192.168.1.149',
    -> master_port='3306',
    -> master_user='backup',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000007',
    -> master_log_pos=431;

#启动从库
mysql> start slave;

#查看主从状态,Slave_IO_Running、Slave_SQL_Running两个都为Yes则成功
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.149
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 431
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

本站博主 , 版权所有丨如未注明 , 均为原创
转载请注明原文链接:Centos7-MySQL 5.7的安装及主从复制【显哥出品,必为精品】
喜欢 (2)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到