diandian
/
shell_mysql.md
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
shell_mysql.md/mysql_install.md

379 lines
9.6 KiB
Raw Permalink Normal View History Unescape

添加 'mysql_install'
1 year ago
##### 1.yum安装数据库
​ 下载mysql的yum仓库
```shell
[root@xingdian ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
```
​ 安装mysql的yum仓库
```shell
[root@xingdian ~]# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
```
​ 修改安装版本：
```shell
方法一：命令方式
[root@xingdian ~]# yum repolist all | grep mysql
[root@xingdian ~]# yum -y install yum-utils
[root@xingdian ~]# yum-config-manager --enable mysql57-community
[root@xingdian ~]# yum-config-manager --disable mysql80-community
方法二：修改文件
[root@xingdian ~]# vim /etc/yum.repos.d/mysql-community.repo
```
​ 安装mysql
```shell
[root@xingdian ~]# yum -y install mysql mysql-server
```
​ 查看初始密码
```shell
[root@xingdian ~]# grep 'password' /var/log/mysqld.log
2019-07-13T15:14:31.176905Z 1 [Note] A temporary password is generated for root@localhost: k12zPB1r;2Ta
```
​ 修改密码
```shell
[root@xingdian ~]# mysqladmin -u root -p'k12zPB1r;2Ta' password 'QianFeng@123'
密码：大小写有特殊字符数字
```
#####
#### 2.主从部署
​ 注意：实验之前环境初始化，不要有残留的数据
##### 环境准备
| 节点 | IP地址 |
| :----: | :--------: |
| Master | 10.0.0.128 |
| Slave | 10.0.0.42 |
注意：
​ 所有节点关闭防火墙和selinux
​ 保证yum仓库可用
​ 保证网络畅通
​ 如果是克隆的服务器需要修改每台数据库的server-uuid
修改主机名：（所有节点）（可选操作）
```shell
[root@xingdian ~]# hostnamectl set-hostname master
[root@xingdian ~]# hostnamectl set-hostname slave
```
添加本地解析：（所有节点）（可选操作）
```shell
[root@master ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.128 master
10.0.0.42 slave
[root@slave ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.128 master
10.0.0.42 slave
```
##### Master部署
安装数据库：（略）
启动数据库：（略）
修改数据库初始密码：（略）
主服务器部署：
```shell
[root@master ~]# vim /etc/my.cnf
log-bin
server-id=1
gtid_mode = ON
enforce_gtid_consistency=1
```
创建授权用户：
```shell
[root@master ~]# mysql -u root -pQianFeng@123
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.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> grant all on *.* to slave@'%' identified by 'QianFeng@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
```
重启服务：
```shell
[root@master ~]# systemctl restart mysqld
```
##### Slave部署
安装数据库：（略）
启动数据库：（略）
修改数据库初始密码：（略）
从服务器部署：
```shell
[root@slave ~]# vim /etc/my.cnf
log-bin
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
relay_log_recovery = on
master-info-repository=TABLE
relay-log-info-repository=TABLE
//这两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
```
重启服务：
```shell
[root@slave ~]# systemctl restart mysqld
```
配置连接主服务器：
```shell
[root@slave ~]# mysql -u root -pQianFeng@123
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 2
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> change master to
-> master_host='master',
-> master_user='slave',
-> master_password='QianFeng@123',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
```
启动Slave：
```shell
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
```
主从状态验证：
```shell
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 369
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 576
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 00813e87-4321-11ed-a33c-000c29311164
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
```
数据验证：
主服务器创建数据：
```shell
[root@master ~]# mysql -u root -pQianFeng@123
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.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> create database qfcloud;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
```
从服务器查验数据：
```shell
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| qfcloud |
| sys |
+--------------------+
5 rows in set (0.00 sec)
```
更新 'mysql_install.md'
1 year ago
##
#### 3.xtrabackup备份
简介：
​ percona-xtrabackup是开源免费的支持MySQL 数据库热备份的软件；能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份；它不暂停服务创建Innodb热备份；为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载
![image-20220925185829126](https://xingdian-image.oss-cn-beijing.aliyuncs.com/xingdian-image/image-20220925185829126.png)
安装软件：
```shell
[root@xingdian ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
[root@xingdian ~]# yum install percona-xtrabackup-24 -y
```
##### 完整备份
​ 创建备份目录：
```shell
[root@xingdian ~]# mkdir -p /xtrabackup/full/
```
​ 备份：
```shell
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full/
```
​ 查看备份数据：
```shell
[root@xingdian ~]# ls /xtrabackup/full/
2022-09-25_19-40-47
```
​ 创建增量备份存放数据目录：
```shell
[root@xingdian ~]# mkdir /xtrabackup/zeng -p
```
​ 模拟数据增加（略）
​ 第一次增量备份：（周二）
```shell
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/full/2022-09-25_19-40-47/
第一次增量备份的数据：
[root@xingdian ~]# ls /xtrabackup/zeng/
2022-09-25_19-56-00
```
​ 模拟数据增加（略）
​ 第二次增量备份：（周三）
```shell
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/zeng/2022-09-25_19-56-00/
第二次增量备份的数据：
[root@xingdian ~]# ls /xtrabackup/zeng/
2022-09-25_19-56-00 2022-09-25_19-58-12
```
​ 后面的增量备份重复上面的操作（略）