MySQL双机
配置两台主机MySQL双活主机：
A:192.168.199.2
B:192.168.199.4
一、安装服务,两台主机执行同样的操作：
root@e2emydb02# tar xvf MySQL-5.6.40-1.el6.x86_64.rpm-bundle.tar
root@e2emydb02# rpm -ivh MySQL-*.rpm --force --nodeps
root@e2emydb02# cp /usr/share/mysql/my-default.cnf /etc/my.cnf
root@e2emydb02# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/e2emydb02.err'.
mysql 5.6 以后 安装时的默认密码存放在目录：/root/.mysql_secret
1、查看密码
root@e2emydb02# more /root/.mysql_secret
The random password set for the root user at Wed May 23 11:30:41 2018 (local time): 5eU4P7fz9Qj_UojJ
2、使用该密码登陆测试：
root@e2emydb02# mysql -u root -p
3、修改密码：
root@e2emydb02# mysqladmin -u root -p password
Enter password:
New password:
Confirm new password:
4、修改数据保存目录
默认安装在/var/lib/mysql/ 目录
修改到自己定义的目录 /etedata
执行以下操作：
root@e2emydb02# service mysql stop
root@e2emydb02# cp -R /var/lib/mysql /etedata
把原目录备份
root@e2emydb02# mv /var/lib/mysql /var/lib/mysql.bak
对/etedata目录赋权
root@e2emydb02# chmod 777 -R /etedata##否则启动会报错： Starting MySQL...The server quit without updating PID file a/mysql/e2emydb01.pid).
错误日志：/etedata/mysql/e2emydb02.err
修改/etc/my.cnf
root@e2emydb02# vi /etc/my.cnf
port=3306
socket=/etedata/mysql/mysql.sock
datadir =/etedata/mysql
socket=/etedata/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION
保存，重启服务
root@e2emydb02# service mysql stop
Shutting down MySQL....
root@e2emydb02# service mysql start
Starting MySQL.
root@e2emydb02# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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>
5、设置服务端、客户端、结果集为UTF-8
mysql> SET character_set_client = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SET character_set_results = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SET character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> create database etedb;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| etedb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
二 配置从 A-->B
至此，基础工作做好了。
下面配置A-->B
1、在A主机操作：
root@e2emydb01#more /etc/my.cnf
port=3306
socket=/etedata/mysql/mysql.sock
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
read-only=0
binlog-do-db=etedb
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=test
auto-increment-increment=2
auto-increment-offset=1
datadir =/etedata/mysql
socket=/etedata/mysql/mysql.sock
完成后需要重启服务器
root@e2emydb01#service mysql stop
Shutting down MySQL....
root@e2emydb01#service mysql start
Starting MySQL.
root@e2emydb01#mysql -u root -p
Enter password:
2、添加用户
为用户授权,只有192.168.199.4可以访问
GRANT USAGE ON . TO 'repl_user'@'192.168.199.2'>****' WITH GRANT OPTION;
grant replication slave on . to 'repl_user'@'192.168.199.4'>****';
执行情况如下：
root@e2emydb01#mysql -u root -p
Enter password:
mysql> GRANT USAGE ON . TO 'repl_user'@'192.168.199.2'>****' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on . to 'repl_user'@'192.168.199.4'>****';
Query OK, 0 rows affected (0.00 sec)
在B主机测试：
root@e2emydb02#mysql -h192.168.199.2 -urepl_user -p****
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> Server version: 5.6.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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>
3、获取服务器初态
在A服务器执行（MYSQL命令行下）锁定表：
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
在A主机执行导出初态：
root@e2emydb01#mysqldump --master-data -uroot -p etedb >etedb.sql
Enter password:
并把文件传到B服务器上：
root@e2emydb01#scp etedb.sql ete@192.168.199.4:/home/ete
ete@192.168.199.4's password:
然后查看初态值并解锁
mysql> show master status\G
1. row
File: mysql-bin.000001
Position: 120
Binlog_Do_DB: etedb
Binlog_Ignore_DB: information_schema,mysql,performance_schema,test
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
注：标红的两个值会在B服务器用到
4、在B服务器执行：
root@e2emydb02#vi /etc/my.cnf
port=3306
socket=/etedata/mysql/mysql.sock
datadir =/etedata/mysql
log-bin=mysql-bin
binlog_format=mixed
server-id= 2
replicate-do-db=etedb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=test
relay_log=mysqld-relay-bin
log-slave-update=yes
socket=/etedata/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION
root@e2emydb02#service mysql stop
Shutting down MySQL....
root@e2emydb02#service mysql start
Starting MySQL.
root@e2emydb02#mysql -u root -p
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.199.2',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='****',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
注：上面几个值是从主服务器得到的
mysql> show slave status\G
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.2
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes ##这两个值是Yes才表示OK
Slave_SQL_Running: Yes ##
Replicate_Do_DB: etedb
Replicate_Ignore_DB: information_schema,mysql,performance_schema,test
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: 120
Relay_Log_Space: 457
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: dd4d3f58-5fcb-11e8-8e32-6c92bf5e6ba0
Master_Info_File: /etedata/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all> 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: 0
1 row in set (0.00 sec)
把刚才从A服务器同步过来的初态文件导入：
root@e2emydb02#cp etedb.sql /rootroot@e2emydb02%5B192.168.199.4%5D%5B/home/ete%5D#cd
页:
[1]