数据库主从+多实例
数据库创建多实例+主从复制
1.准备多个配置文件
创建多个存放配置文件的目录
[root@db02 ~]# mkdir /data/{13306,23306,33306} -p
[root@db02 ~]# tree /data/
/data/
├── 13306
├── 23306
└── 33306
创建多个配置文件
[root@db02 data]# cat 13306/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/13306/data
socket=/data/13306/data/mysql.sock
port=13306
log-bin=/data/13306/data/mysql-bin
log_error=/data/13306/data/13306.err
server_id=1
pid_file=/data/13306/data/13306.pid
--------------------------------------
[root@db02 data]# cat 23306/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/23306/data
socket=/data/23306/data/mysql.sock
port=23306
log-bin=/data/23306/data/mysql-bin
log_error=/data/23306/data/23306.err
server_id=2
pid_file=/data/23306/data/23306.pid
----------------------------------------
初始化
[root@db02 ~]# cd /application/mysql/scripts/
#初始化13306的数据目录
[root@db02 scripts]#./mysql_install_db --defaults-file=/data/13306/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/13306/data
#23306
[root@db02 scripts]# ./mysql_install_db --defaults-file=/data/23306/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/23306/data
#3306
[root@db02 scripts]#. ./mysql_install_db --defaults-file=/data/33306/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3306/data
[root@db02 scripts]# tree -L 2 /data
├── 3307
│ ├── data
│ └── my.cnf
├── 3308
│ ├── data
│ └── my.cnf
└── 3309
├── data
└── my.cnf
启动
[root@db02 scripts]# --defaults-file=/data/3307/my.cnf &
[root@db02 scripts]# --defaults-file=/data/3308/my.cnf &
[root@db02 scripts]# --defaults-file=/data/3309/my.cnf &
检查端口
[root@db01 data]# netstat -lntup|grep 330
tcp6 0 0 :::3306 :::* LISTEN 8480/mysqld
tcp6 0 0 :::23306 :::* LISTEN 8061/mysqld
tcp6 0 0 :::33306 :::* LISTEN 8399/mysqld
tcp6 0 0 :::13306 :::* LISTEN 7398/mysqld
设置密码
[root@db02 scripts]# mysqladmin -uroot -p -S/data/13306/data/mysql.sock password '13306'
[root@db02 scripts]# mysqladmin -uroot -p -S/data/23306/data/mysql.sock password '23306'
[root@db01 scripts]# mysqladmin -uroot -p -S/data/33306/data/mysql.sock password '33306'
连接MySQL
[root@db02 scripts]# mysql -uroot -p13306 -S /data/13306/data/mysql.sock
[root@db02 scripts]# mysql -uroot -p23306 -S /data/23306/data/mysql.sock
[root@db02 scripts]# mysql -uroot -p33306 -S /data/33306/data/mysql.sock
查看
[root@db01 scripts]# mysql -uroot -p13306 -S /data/23306/data/mysql.sock
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.6.44-log Source distribution
Copyright (c) 2000, 2019, 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 variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
测试成功了就可以做主从了
在主的数据库设置以下
[root@db01 data]# vim /etc/my.cnf
开启日志：
log_bin=mysql-bin
打开端口
设置server_id
port = 3306
server_id = 5
打开主的数据打开
#创建一个用户
mysql> grant replication slave on *.* to rep@'10.0.0.51' identified by '123';
Query OK, 0 rows affected (0.03 sec)
#展示主数据库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 325 | | | |
+------------------+----------+--------------+------------------+----
准备从数据库了
打开从数据库
#输入以下信息
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=325,
-> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.20 sec)
#打开
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#显示状态 看到两个yes 就可以了
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 325
Relay_Log_File: 13306-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-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: 325
Relay_Log_Space: 456
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: 5
Master_UUID: 81b282ac-faf3-11e9-8745-000c29ce34f8
Master_Info_File: /data/13306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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)
其他的从数据库也是一样的操作
测试主从是否完成
在主的的数据库创建一个用户，然后咋从的数据库上查看，就说明成功了
