MEC的Mysql主备创建过程

最新推荐文章于 2021-12-17 00:33:19 发布

weixin_42293800

最新推荐文章于 2021-12-17 00:33:19 发布

阅读量2.1k

点赞数

本文链接：https://blog.csdn.net/weixin_42293800/article/details/107026640

版权

1. 文档概要... 5

2. 1、CentOS镜像安装mysql数据库：... 5

2.1.1. 数据库安装获取初始密码... 5

2.1.2. 数据库root权限修改... 6

2.1.3. 导入MEC数据库... 7

2.1.4. 配置mysql登录软连接... 8

2.1.5. 创建数据库应用账号和赋权... 8

3. MySQL主从配置... 9

3.1. 主MYSQL配置... 9

3.1.1. 给从数据库设置授权用户（创建复制账号）... 9

3.1.2. 修改主mysql配置，在my.cnf中添加一下内容... 9

3.1.3. 重启mysql服务... 9

3.1.4. 登录mysql 10

3.1.5. 查看主服务器上当前的二进制日志名和偏移量值... 10

3.2. 从MYSQL配置... 10

3.2.1. 修改my.cnf配置... 10

3.2.2. 重启mysql服务... 10

3.2.3. 登录mysql 10

3.2.4. 建立从mysql与主mysql连接... 10

3.2.5. 开启slave. 11

3.2.6. 查询slave状态... 11

3.2.7. 验证是否可以正常同步... 11

4. 注意事项：... 13

4.1. 注意事项1：mysql服务重启问题：... 13

4.2. 注意事项2：my.cnf文件问题：... 14

5. 参考文献... 14

文档概要

安装MEC环境时需要安装mysql主备数据库，初步接触该的同学由于不熟悉，现有文档太过简单，网上文档又比较杂乱，这些写一个Mysql主备安装文档对小白同学进行扫盲，大神绕行。

CentOS镜像安装mysql数据库：
1. 数据库安装获取初始密码

上传好mysql安装包，在root目录下，执行红色部分进行数据库安装，完成后获取数据库初始密码：

[root@localhost scripts]# ls

load_mec.sh mysql_install.sh sh_bak

[root@localhost scripts]# pwd

/root/mysql/scripts

[root@localhost scripts]# ./mysql_install.sh

successful, mysql user is already added!

successful, mysql.tar is already uncompress!

2020-06-23T09:56:06.776169Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2020-06-23T09:56:06.995261Z 0 [Warning] InnoDB: New log files created, LSN=45790

2020-06-23T09:56:07.035823Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2020-06-23T09:56:07.095571Z 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: c201ea5c-b537-11ea-ae09-fa163e2c69d4.

2020-06-23T09:56:07.097523Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2020-06-23T09:56:07.097988Z 1 [Note] A temporary password is generated for root@localhost: rAe_i+qdw5tT

successful, mysql is already initialed!

successful, mysql is already started!

[root@localhost scripts]#

1. 数据库root权限修改

登录mysql修改root密码和远程登录权限，执行以下步骤：

需要执行的命令：

/opt/mysql/mysql-5.7.24/bin/mysql -P3301 -p -S /data/mysql3301/mysql.sock

ALTER USER USER() IDENTIFIED BY 'Mepdev!@#456';

use mysql;

update user set host='127.0.0.1' where user = 'root';

FLUSH PRIVILEGES;

操作实例：

[root@localhost scripts]# /opt/mysql/mysql-5.7.24/bin/mysql -P3301 -p -S /data/mysql3301/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.24-log

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> ALTER USER USER() IDENTIFIED BY '******';

Query OK, 0 rows affected (0.00 sec)

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 host='127.0.0.1' where 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)

1. 1. 导入MEC数据库

使用load_mec.sh脚本导入MEC数据库：

source scripts/load_mec.sh

导入数据库实例：

[root@localhost scripts]# source /root/mysql/scripts/load_mec.sh

mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+

| Database |

+--------------------+

| information_schema |

| amsdb |

| mecs-1.0 |

| meomdb-1.0 |

| mysql |

| mysqllog |

| omsdb |

| performance_schema |

| sys |

| vm-operator |

+--------------------+

successful, mec sql is imported!

1. 1. 配置mysql登录软连接

找出安装目录做如下操作，进行数据库登录软连接：

[root@localhost scripts]# cd /usr/local/bin

[root@localhost bin]# ln -fs /opt/mysql/mysql-5.7.24/bin/mysql mysql

1. 1. 创建数据库应用账号和赋权

创建应用账号赋权：

mysql -uroot -p -h127.0.0.1 -P3301

GRANT ALL PRIVILEGES ON `vm-operator`.* TO 'mecs_user'@'%'identified by '******';

GRANT ALL PRIVILEGES ON `meomdb-1.0`.* TO 'mecs_user'@'%' identified by '******';

GRANT ALL PRIVILEGES ON `amsdb`.* TO 'mecs_user'@'%' identified by '******';

GRANT ALL PRIVILEGES ON `omsdb`.* TO 'mecs_user'@'%' identified by '******';

GRANT ALL PRIVILEGES ON `mecs-1.0`.* TO 'mecs_user'@'%' identified by '******';

验证应用账号：

Mysql -umecs_user -p‘密码’ -h127.0.0.1 -P3301

登录成功，则数据库安装完成。

MySQL主从配置

场景：

MySQL01：192.168.0.184 主

MySQL02：192.168.0.63 从

1. 主MYSQL配置
  1. 给从数据库设置授权用户（创建复制账号）

grant replication slave on *.* to 'mecs_user'@'192.168.0.63' identified by 'Mepdev!@#456';

flush privileges;

1. 1. 修改主mysql配置，在my.cnf中添加一下内容

server-id=1

log_bin=master_bin

log_slave_updates=true

注意：这里的my.cnf是由/opt/mysql中通拷贝来的，/opt/mysql中中的my.cnf有改动，按照“注意事项2”操作。

1. 1. 重启mysql服务

[root@localhost ~]# service mysqld restart

注意：如无法重启请按照“注意事项1”操作。

1. 1. 登录mysql

mysql -uroot -p -h127.0.0.1 -P3301

1. 1. 查看主服务器上当前的二进制日志名和偏移量值

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

+-------------------+----------+--------------+------------------+-------------------+

| master_bin.000001 | 154 | | | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

1. 从MYSQL配置
  1. 修改my.cnf配置

vi /etc/my.cnf

-------------------添加以下内容--------------------

server-id=3

relay_log=relay-log-bin

relay_log_index=slave-relay-bin.index

注意：按照“注意事项2”操作。

1. 1. 重启mysql服务

[root@localhost ~]# service mysqld restart

注意：如无法重启请按照“注意事项1”操作。

1. 1. 登录mysql

mysql -uroot -p -h127.0.0.1 -P3301

1. 1. 建立从mysql与主mysql连接

change master to master_host='192.168.0.184',master_port=3301,master_user='mecs_user',master_password='Mepdev!@#456',master_log_file='master_bin.000001',master_log_pos=154;

注： master_host是主节点的ip地址，master_user 和master_password主服务器创建的用于连接主服务器的帐号和密码,master_log_file和master_log_pos需要从master节点，进入Mysql数据库后进行查询.

1. 1. 开启slave

start slave;（关闭：stop slave）

1. 1. 查询slave状态

show slave status \

1. 1. 验证是否可以正常同步

过程：在主mysql创建一个数据库，去从数据库里面看看是否同步过去了。

主上创建数据库：

备上也备份：

注意事项：
1. 注意事项1：mysql服务重启问题：

mysql服务无法重启解决方法：

[root@ecs-6488-0618143 etc]# service mysql restart

Redirecting to /bin/systemctl restart mysql.service

Failed to restart mysql.service: Unit not found.

[root@ecs-6488-0618143 etc]# systemctl restart mysql

Failed to restart mysql.service: Unit not found.

[root@ecs-6488-0618143 etc]# /etc/init.d/ | grep mysql

-bash: /etc/init.d/: Is a directory

You have new mail in /var/spool/mail/root

[root@ecs-6488-0618143 etc]# find / -name mysql.server

/opt/mysql/mysql-5.7.24/support-files/mysql.server

/opt/mysql/mysql-5.7.24/mysql-5.7.24-el7-x86_64/support-files/mysql.server

[root@ecs-6488-0618143 etc]# cp /opt/mysql/mysql-5.7.24/support-files/mysql.server /etc/init.d/mysql

[root@ecs-6488-0618143 etc]#

[root@ecs-6488-0618143 etc]# service mysql start

Starting MySQL SUCCESS!

[root@ecs-6488-0618143 etc]# service mysql restart

Shutting down MySQL.... SUCCESS!

Starting MySQL.......... SUCCESS!

[root@ecs-6488-0618143 etc]#

1. 注意事项2：my.cnf文件问题：

内存配置：

配置文件 innodb_buffer_pool 参数，要根据内存配置一下

你就配内存的三分之二，还有其它点根据这个放开：

一个是/opt/mysql/my.cnf下文件权限问题，两个目录下都做下权限修改：

my.cnf 权限有问题

chmod 644 my.cnf

[root@ecs-6488-0618145 mysql]# chmod 644 my.cnf

[root@ecs-6488-0618145 mysql]# cp my.cnf /etc/my.cnf

[root@ecs-6488-0618145 mysql]# cd /etc/

[root@ecs-6488-0618145 etc]# chmod 644 my.cnf

[root@ecs-6488-0618145 etc]#

[root@ecs-6488-0618145 etc]# service mysqld restart

Shutting down MySQL.... SUCCESS!

Starting MySQL... SUCCESS!

参考文献

https://blog.csdn.net/u010004734/article/details/94450563

https://www.cnblogs.com/zengpeng/p/11176171.html

weixin_42293800

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MEC的Mysql主备创建过程

目录目录1. 文档概要... 52. 1、CentOS镜像安装mysql数据库：... 52.1.1. 数据库安装获取初始密码... 52.1.2. 数据库root权限修改... 62.1.3. 导入MEC数据库... 72.1.4. 配置mysql登录软连接... 82.1.5. 创建数据库应用账号和赋权... 83. MySQL主从配置... 93.1. 主MYSQL配置......
复制链接

扫一扫