11、登录数据库以添加远程登录帐号信息
[MySQL@mysqldb1 ~]$ mysql -u root -p -P 3306 --socket=/tmp/mysql3306.sock
[mysql@mysqldb2 ~]$ mysql -u root -p -P 3306 --socket=/tmp/mysql3306.sock
[mysql@mysqldb3 ~]$ mysql -u root -p -P 3306 --socket=/tmp/mysql3306.sock
注：使用mysql用户执行上述命令前，先配置相关环境变量值
/home/mysql/.bash_profile
添加如下内容：
MYSQL3306HOME=/mysql/3306
PATH=$PATH:$MYSQL3306HOME/bin
export MYSQL3306HOME PATH
注：上述登录数据库是需有参数 --socket ,否则会报出如下错误:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
--为了安全起见，先删除默认生成的用户相关信息在创建一个指定名称的数据库管理员帐号
mysql> set password for root@localhost = password('rootroot');
mysql> delete from mysql.db;
mysql> delete from mysql.user;
mysql> grant all privileges on *.* to 'sysadmin'@'localhost' identified by 'mysql' with grant option;
mysql> grant all privileges on *.* to 'sysadmin'@'127.0.0.1' identified by 'mysql' with grant option;
mysql> flush privileges;
mysql> select host,user from user where user='sysadmin';
--退出当前连接，再次登录数据库，以验证新建数据库管理员帐号的合法性
--以 MySQLDB1 节点为例测试
mysql> exit;
[mysql@mysqldb1 ~]$ mysql -h localhost -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
--注：为了安全的需要，上述的连接数据库命令行中密码没有直接写出，而是在使用 -p 参数，再在提示符行中输入密码
确认有当前添加的用户即可进行远程登录数据库。
12、设置数据库自动启动
--MySQLDB1 节点
[root@mysqldb1 ~]# chkconfig --add mysql3306
[root@mysqldb1 ~]# chkconfig mysql3306 --list
mysql3306 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@mysqldb1 ~]#
--MySQLDB2 节点
[root@mysqldb2 ~]# chkconfig --add mysql3306
[root@mysqldb2 ~]# chkconfig mysql3306 --list
mysql3306 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@mysqldb2 ~]#
--MySQLDB3 节点
[root@mysqldb3 ~]# chkconfig --add mysql3306
[root@mysqldb3 ~]# chkconfig mysql3306 --list
mysql3306 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@mysqldb3 ~]#
13、重启系统验证数据库自动启动服务功能
--重启系统
[root@mysqldb1 ~]# shutdown -r now
[root@mysqldb2 ~]# shutdown -r now
[root@mysqldb3 ~]# shutdown -r now
--查看数据库服务是否随系统启动
--MySQLDB1 节点
[root@mysqldb1 ~]# service mysql3306 status
MySQL running (2122) [ OK ]
[root@mysqldb1 ~]#
--MySQLDB2 节点
[root@mysqldb2 ~]# service mysql3306 status
MySQL running (2117) [ OK ]
[root@mysqldb2 ~]#
--MySQLDB3 节点
[root@mysqldb3 ~]# service mysql3306 status
MySQL running (2119) [ OK ]
[root@mysqldb3 ~]#
14、在master节点创建复制专业帐号
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
15、查看 master 主机信息 （slave 服务器配置需要改信息）
[mysql@mysqldb1 ~]$ mysql -h localhost -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show master status \G
*************************** 1. row ***************************
File: mysql_bin.000004
Position: 403
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>
16、在 MySQLDB2 节点配置复制，并启动复制
mysql> show slave status \G
Empty set (0.00 sec)
mysql> change master to
-> master_host='192.168.1.41',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql_bin.000004',
-> master_log_pos=403;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
mysql>
17、复制功能测试
--MySQLDB1 节点
mysql> create database testdb;
mysql> use testdb;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| testdb |
+------------+
1 row in set (0.00 sec)
mysql> create table t1(id int, name varchar(20));
Query OK, 0 rows affected (1.71 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> insert into t1 (id,name) values(1,'yangchao');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | yangchao |
--MySQLDB1 节点
mysql> select * from t1;
Empty set (0.01 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | yangchao |
+------+----------+
1 row in set (0.00 sec)
mysql>
----------------------- 复制功能正常 --------------------------------------