msyql主备安装

最新推荐文章于 2023-07-22 18:14:59 发布

ReadMeFrist

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量344

点赞数 1

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/jy8655790/article/details/119084180

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

准备

master	172.16.3.89
slave	172.16.3.90

安装mysql（两台服务器相同）

1.下载msyql
地址：http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.gz
2.下载解压

cd /usr/local/src
wget  http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-8.0.23-linux-glibc2.12-x86_64.tar.gz

3.移动文件夹

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

4.创建数据目录

cd /usr/local/mysql
mkdir data

5.创建mysql用户和组，并且给文件加添加用户和组

groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql

6.初始化数据库

mkdir mysql_install_db
chmod 777 mysql_install_db 
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

初始化后记录密码：
2021-07-25T09:30:02.157926Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 2BJ6xuglQv,E
7.添加或修改 my.cnf

vi  etc/my.cnf

修改为：

[mysqld]
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   default_authentication_plugin=mysql_native_password
[client]
   socket = /usr/local/mysql/mysql.sock
   default-character-set=utf8

8.添加msyql服务，并设置开机启动

cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysqld   ##拷贝服务
chmod +x /etc/init.d/mysqld            
chkconfig --add mysqld               ##设置开会启动服务

9.修改环境变量

vi /etc/profile

最下面添加以下：

export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib

立即生效环境变量

source /etc/profile

10.启动服务，检查服务

 service mysqld start

[root@qfc-ntp support-files]# ps -ef | grep mysql
root        2620       1  0 17:50 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/qfc-ntp.pid
mysql       2808    2620  0 17:50 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=qfc-ntp.err --pid-file=/usr/local/mysql/data/qfc-ntp.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root        3075    2202  0 17:54 pts/0    00:00:00 grep --color=auto mysql

11.登录mysql

[root@qfc-ntp ~]# mysql -uroot -p          
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

查看依赖服务

[root@qfc-ntp ~]# cd /usr/local/mysql/bin/
[root@qfc-ntp bin]# ldd mysql             
        linux-vdso.so.1 (0x00007ffc7b1bf000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f784b25e000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f784b056000)
        libcrypto.so.1.1 => /usr/local/mysql/bin/./../lib/private/libcrypto.so.1.1 (0x00007f784aba4000)
        libssl.so.1.1 => /usr/local/mysql/bin/./../lib/private/libssl.so.1.1 (0x00007f784a914000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f784a710000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f784a4f9000)
        libtinfo.so.5 => not found
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f784a164000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f7849de2000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7849bca000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7849805000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f784b47e000)

创建软连接

ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5

再次确认
libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00007fa619102000)
使用初始密码登录：

 mysql -uroot -p2BJ6xuglQv,E

修改初始面（要么无法操作）：

mysql> alter user root@localhost identified by '123456';

12远程登录

mysql> use mysql;
Database changed
mysql> update user set host='%' where user='root' limit 1;
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)

添加防火墙

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

Navicat测试连接：
在这里插入图片描述

配置主从复制

1.主库数据库配置
修改my.cnf
[root@Zabbix ~]# cat /etc/my.cnf

[mysqld]
    basedir = /usr/local/mysql   
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   default_authentication_plugin=mysql_native_password
   server-id = 1
   log-bin = mysql-bin
   binlog_format = mixed 

   max_binlog_size = 100m #binlog每个日志文件大小
   binlog_cache_size = 4m 
   max_binlog_cache_size= 512m 
   binlog-ignore-db=mysql 
   slave-skip-errors = all 
[client]
   socket = /usr/local/mysql/mysql.sock
   default-character-set=utf8

进入master的数据库，为master创建复制用户

create user 'slaveuser'@'172.16.3.%' identified by 'ysp_password';
grant replication slave on *.* to 'slaveuser'@'172.16.3.%';
FLUSH PRIVILEGES;

锁表备份传输到备机

mysql>flush  tables  with  read  lock;
show  variables  like  "%timeout%";   ###查看锁表时间

mkdir  /server/backup/  -p  
mysqldump  -uroot  -p"123456"     -A   > /server/backup/mysql_bak.sql ##全量备份
scp /server/backup/mysql_bak.sql root@172.16.3.90:/usr/local/src

查看master状态记录下mysql-bin和Position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      156 |              | mysql            |                   |
+------------------+----------+--------------+------------------+----------              
1 row in set (0.00 sec)

mysql>unlock  tables;   ###导出记录后解锁

2.设置从数据库
修改my.cnf

[root@qfc-ntp ~]# cat /etc/my.cnf 
[mysqld]
    basedir = /usr/local/mysql   
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   default_authentication_plugin=mysql_native_password
  server-id =2
  log-bin=mysql-bin
  [client]
   socket = /usr/local/mysql/mysql.sock
   default-character-set=utf8

重启从数据库

server mysqld restart

还原从数据库

 cd /usr/local/src/
 mysql  -uroot  -p"123456"  <  mysql_bak.sql

设置主从

CHANGE  MASTER  TO
MASTER_HOST = '172.16.3.89',
MASTER_USER = 'slaveuser',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;

start  slave;

查看状态

mysql> show  slave  status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.3.89
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 25984174
               Relay_Log_File: qfc-ntp-relay-bin.000004
                Relay_Log_Pos: 25984389
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Slave_IO_Running:和 Slave_SQL_Running显示yes，表示主备运行正常
3.测试
主机新建一个数据库备机也同步
主机新建数据库

create  database  diablo4;

测试备机是否正常

show  databases  like  'diablo4';

参考

https://blog.csdn.net/github_39533414/article/details/80144890
https://www.cnblogs.com/jianmingyuan/p/10903682.html
https://blog.51cto.com/wanghaipeng1124/874651

ReadMeFrist

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
msyql主备安装

安装1.下载msyql地址：http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.gz2.下载解压cd /usr/local/srcwget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.gztar -zxvf mysql-8.0.23-li
复制链接

扫一扫