企业级MySQL数据库多实例实战【显哥出品，必为精品】

一、mysql多实例

1.创建多实例目录

[root@db01 ~]# mkdir /data/{3307,3308,3309} -p

2.配置多实例配置文件

[root@db01 ~]# vim /data/3307/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/data/mysql.err
log-bin=/data/3307/data/mysql-bin
server_id=7

[root@db01 ~]# vim /data/3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
port=3308
log-error=/data/3308/data/mysql.err
log-bin=/data/3308/data/mysql-bin
server_id=8

[root@db01 ~]# vim /data/3309/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/data/mysql.err
log-bin=/data/3309/data/mysql-bin
server_id=9

3.初始化多套数据库

[root@db01 ~]# cd /usr/local/mysql/scripts/

[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data

[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data

[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

#查看初始化后目录结构
[root@db01 data]# tree -L 3 /data/

4.授权目录

[root@db01 data]# chown -R mysql.mysql /data/

5.启动数据库

[root@db01 data]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01 data]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 data]# mysqld_safe --defaults-file=/data/3309/my.cnf &

#如果启动不起来，停掉3306数据库，再尝试启动

6.验证启动

[root@db01 data]# netstat -lntp       
tcp6       0      0 :::3307                 :::*                    LISTEN      31358/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      31531/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      32042/mysqld     

7.连接多实例

[root@db01 data]# mysql -S /data/3307/mysql.sock
[root@db01 data]# mysql -S /data/3308/mysql.sock
[root@db01 data]# mysql -S /data/3309/mysql.sock

8.确认多实例

[root@db01 data]# mysql -S /data/3309/mysql.sock -e 'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+

[root@db01 data]# mysql -S /data/3308/mysql.sock -e 'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+

[root@db01 data]# mysql -S /data/3307/mysql.sock -e 'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 7     |
+---------------+-------+

9.多实例配置密码

[root@db01 data]# mysqladmin -uroot -S /data/3307/mysql.sock password '3307'

[root@db01 data]# mysqladmin -uroot -S /data/3308/mysql.sock password '3308'

[root@db01 data]# mysqladmin -uroot -S /data/3309/mysql.sock password '3309'

10.学习阶段连接数据库

[root@db01 data]# vim /usr/bin/mysql3307
mysql -uroot -S /data/3307/mysql.sock -p3307
[root@db01 data]# vim /usr/bin/mysql3308
mysql -uroot -S /data/3308/mysql.sock -p3308
[root@db01 data]# vim /usr/bin/mysql3309
mysql -uroot -S /data/3309/mysql.sock -p3309

[root@db01 data]# chmod +x /usr/bin/mysql*

二、mysql多实例主从

1.主库创建主从同步的用户

mysql> grant replication slave on *.* to rep@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.01 sec)

2.主库查看binlog和位置点

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      467 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

3.从库配置同步的主库信息

#从库需要知道：
	1.主库是谁 127.0.0.1
	2.通过什么用户去同步主库 rep
	3.主从的用户的密码 123
	4.binlog名字是什么 mysql-bin.000004
	5.binlog位置点 467
	
mysql> change master to
    -> master_host='127.0.0.1',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=467,
    -> master_port=3307;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

change master to
master_host='127.0.0.1',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000004',
master_log_pos=467,
master_port=3307;

4.开启从库

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5.查看主从状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 467
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes