MEC的Mysql主备创建过程

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 [email protected]: rAe_i+qdw5tT

successful, mysql is already initialed!

successful, mysql is already started!

[[email protected] 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 '[email protected]#456';

use mysql;

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

FLUSH PRIVILEGES;

操作实例：

[[email protected] 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

导入数据库实例：

[[email protected] 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登录软连接

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

[[email protected] scripts]# cd /usr/local/bin

[[email protected] 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 '[email protected]#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服务

[[email protected] ~]# 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服务

[[email protected] ~]# 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='[email protected]#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数据库后进行查询.