centos7 下mysql5.7.26 安装和主备复制

sinat_16800363

已于 2022-03-06 21:23:36 修改

阅读量886

点赞数

文章标签： mysql 数据库服务器

于 2022-01-29 10:25:50 首次发布

本文链接：https://blog.csdn.net/sinat_16800363/article/details/122731571

版权

1、mysql5.7.26 安装

1、解压复制到 /usr/local/ 下

[root@Xxxx-yw ~]# cd /home/soft/
[root@Xxxx-yw soft]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@Xxxx-yw ~ soft]# mv mysql-5.7.25-linux-glibc2.12-x86_64 mysql
[root@Xxxx-yw ~  soft]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql
[root@Xxxx-yw ~  soft]# mv mysql /usr/local/

2、创建文件夹后续配置/etc/my.cnf 用到

[root@Xxxx-yw ~  soft]# cd /usr/local/
[root@Xxxx-yw ~ mysql]# mkdir data
[root@Xxxx-yw ~ mysql]# mkdir tmp
[root@Xxxx-yw ~ mysql]# mkdir mariadb
[root@Xxxx-yw ~ mysql]# cd mariadb/
[root@Xxxx-yw ~ mariadb]# touch mariadb.log

3、添加mysql组合用户、赋予权限

[root@Xxxx-yw ~ local]# cat /etc/group | grep mysql
[root@Xxxx-yw ~ local]# groupadd mysql
[root@Xxxx-yw ~ local]# useradd -r -g mysql mysql
[root@Xxxx-yw ~ local]# chown -R mysql:mysql mysql/
[root@Xxxx-yw ~ local]# chmod -R 755 mysql/

4、初始化安装 mysql

[root@Xxxx-yw ~ local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

执行完上面之后，最后一行会有默认生成的密码，记下来。最后有个默认密码

5、配置/etc/my.cnf

[root@Xxxx-yw ~ local]# vi /etc/my.cnf
[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
tmpdir=/usr/local/mysql/tmp
symbolic-links=0
#聚合函数问题
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
#表名大小问题
lower_case_table_names=1

[mysqld_safe]
log-error=/usr/local/mysql//mariadb/mariadb.log
pid-file=/usr/local/mysql/mariadb/mariadb.pid
[mysql]
default-character-set=utf8mb4
[client]
socket=/usr/local/mysql/tmp/mysql.sock
default-character-set=utf8mb4

6、复制启动脚本到资源目录

[root@Xxxx-yw ~  local]# cd mysql/
[root@Xxxx-yw ~  mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

7、增加mysqld服务控制脚本执行权限，将mysqld服务加到系统服务中，开启自启动，并服务检查是否生效

[root@Xxxx-yw ~  mysql]# chmod +x /etc/rc.d/init.d/mysqld
[root@Xxxx-yw ~  mysql]# chkconfig --add mysqld
[root@Xxxx-yw ~  mysql]# chkconfig --list mysqld
mysqld         0:off 1:off 2:on 3:on 4:on 5:on 6:off

8、执行启动

[root@Xxxx-yw ~ local]# service mysqld start

9、进入mysql命令模式，密码是之前初始化生成的。

[root@Xxxxx-yw ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin
[root@Xxxxx-yw ~]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
[root@Xxxxx-yw ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26

Copyright (c) 2000, 2019, 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>

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

10、设置新密码

mysql> alter user root@'localhost' identified with mysql_native_password by 'i9CO!FtKXXXX';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;

11、开启远程连接并查看开启的情况

mysql> use mysql;
Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'i9CO!FtKXXXX' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
|               |           |
+---------------+-----------+
4 rows in set (0.00 sec)

mysql> exit;

12、与防火墙有关端口开启

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

--------------------------------------------------------------------------------------------------------------------------------

2、主备配置

vi /etc/my.cnf

1:主机配置 /etc/my.cnf

[mysqld]
port=3306
basedir=/approot/local/mysql
datadir=/approot/local/mysql/data
socket=/approot/local/mysql/tmp/mysql.sock
user=mysql
tmpdir=/approot/local/mysql/tmp

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
lower_case_table_names=1
#bin-logs 过期时间
expire_logs_days=180

#------------- 主从复制配置 start ----------------
log-bin = /approot/local/mysql/logs/mysql-bin   # 开启二进制日志,保存到.../mysql-bin中
server-id = 11                           # 设置主服务器的ID(不能重复，建议使用ip的最后>一段)

server-id = 11                           # 设置主服务器的ID(不能重复，建议使用ip的最后>一段)
innodb_flush_log_at_trx_commit=1           # 每次commit 日志缓存中的数据刷到磁盘中。通>常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能
力
sync_binlog=1                              # 开启binlog同步功能，当每进行n次事务提交之>后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
# binlog-ignore-db 表示同步的时候忽略的数据库，忽略几个就写几个，如果使用binlog-do-db指
定要同步的数据库，这些可以不设置
#binlog-ignore-db=information_schema

#------------- 主从复制配置 end ---------------


[mysqld_safe]
log-error=/approot/local/mysql/data/error.log
pid-file=/approot/local/mysql/data/mysql.pid

[mysql]
default-character-set=utf8mb4

[client]
socket=/approot/local/mysql/tmp/mysql.sock
default-character-set=utf8mb4

#需要重启
service mysql restart

service mysql start

service mysql stop

service mysql status

2:重启Mysql

创建
[root@localhost local]#mkdir logs
[root@localhost local]# chown -R mysql mysql
[root@localhost local]# chgrp -R mysql mysql

# service mysql restart
Shutting down MySQL....                                    [  OK  ]
Starting MySQL.                                            [  OK  ]

mysql> show global variables like '%log_bin%'; 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password('t***333');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to root@'%' identified by 't***333';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> 
 
# 查看二进制日志是否开启
msyql> show global variables like '%log_bin%'; 
+---------------------------------+-------------------------------------------+
| Variable_name                   | Value                                     |
+---------------------------------+-------------------------------------------+
| log_bin                         | ON                                        |
| log_bin_basename                | /approot/local/mysql/logs/mysql-bin       |
| log_bin_index                   | /approot/local/mysql/logs/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                       |
| log_bin_use_v1_row_events       | OFF                                       |
+---------------------------------+-------------------------------------------+
 
# 查看主节点二进制日志列表 （以后会有多个）
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       830 |
+------------------+-----------+
1 row in set (0.00 sec)
 
# 查看主节点的serverid
mysql> show global variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| character_set_server            | utf8mb4                              |
| collation_server                | utf8mb4_general_ci                   |
| innodb_ft_server_stopword_table |                                      |
| server_id                       | 142                                  |
| server_id_bits                  | 32                                   |
| server_uuid                     | 75ad17c1-7e59-11ec-b248-fa163ec03517 |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)

3:设置备库的访问权限

// 创建用户：root  密码：mysql,可以创建应用用户的根据自己的情况， ip为备库的ip
grant FILE on *.* to 'root'@'127.1.1.11' identified by 'mysql';
grant replication slave on *.* to 'root'@'127.1.1.11' identified by 'mysql';
flush privileges;

//
show master status\G;

[root@localhost mysql]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@vm]# mysql -u root -p
mysql> use mysql;
msyql> show global variables like '%log_bin%'; 



mysql>  show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>



mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1584 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql>

4:备库的配置 /etc/my.cnf

[mysqld]
port=3306
basedir=/approot/local/mysql
datadir=/approot/local/mysql/data
socket=/approot/local/mysql/tmp/mysql.sock
user=mysql
tmpdir=/approot/local/mysql/tmp

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
lower_case_table_names=1

log-bin = /approot/local/mysql/logs/mysql-bin
server-id = 27              # 一定不要和主节点的id重复

log-slave-updates = 1 #将从服务器从主服务器收到的更新记入到从服务器自己的二进制>日志文件中。
slave-skip-errors=all
slave-net-timeout=60

expire_logs_days=180


[mysqld_safe]
log-error=/approot/local/mysql/data/error.log
pid-file=/approot/local/mysql/data/mysql.pid

[mysql]
default-character-set=utf8mb4

[client]
socket=/approot/local/mysql/tmp/mysql.sock
default-character-set=utf8mb4

mysql> stop slave;   //现暂停同步
//ip 为主库的ip 用户名和密码是主库里面设置备库信息访问信息
mysql> change master to master_host='127.0.1.11',master_user='root',master_password='mysql',master_log_file='mysql-bin.000004', master_log_pos=4564;
mysql> start slave;	  //开启同步


 show slave status\G;

5:验证是否成功

mysql -uroot -p

create database test;

6:在备库看产

show databases;

7:在主库执行

mysql> use test;
mysql> create table sk_test(
    id int(10) not null auto_increment,
    username varchar(20) default null,
    password varchar(60) default null,
    create_time datetime default null,
    primary key (id)
    ) engine=InnoDB charset=utf8mb4;

8:在备库查看

mysql> use test;
Database changed
mysql> show tables;

9:主库执行

drop tables sk_test;
Query OK, 0 rows affected (0.01 sec)

10:备库执行

 show tables;

#1 主库执行
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)

mysql>

#2从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>