一、 下载安装包
从mysql官网下载安装包解压,本文解压到/usr/local/mysql目录,并配置环境变量:
vim ~/.bash_profile
增加一行
export PATH=$PATH:/usr/local/mysql/bin
保存后,source ~/.bash_profile使配置生效。
二、初始化实例
1、创建两个实例目录:
mkdir -p /usr/local/mysql/mysql3307/data
mkdir -p /usr/local/mysql/mysql3308/data
2、初始化数据
--initialize-insecure表示初始密码为空,注意不能使用root用户执行,可能需要给实例目录赋权限。
mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql3307/data --initialize-insecure
mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql3308/data --initialize-insecure
三、通过mysqld_multi管理多个实例
1、配置my_multi.cnf文件
vim /etc/my_multil.cnf
[mysqld_multi]
user = root
password =
[mysqld3307]
server-id=3307
port=3307
log_bin=mysql-bin
binlog-format=Row
basedir=/usr/local/mysql/mysql3307
datadir=/usr/local/mysql/mysql3307/data
socket=/tmp/mysql_3307.sock
mysqlx_socket=/tmp/mysql_3307x.sock
log-error=/usr/local/mysql/mysql3307/log
[mysqld3308]
server-id=3308
port=3308
log_bin=mysql-bin
binlog-format=Row
basedir=/usr/local/mysql/mysql3308
datadir=/usr/local/mysql/mysql3308/data
socket=/tmp/mysql_3308.sock
mysqlx_socket=/tmp/mysql_3308x.sock
log-error=/usr/local/mysql/mysql3308/log
read_only=1
[mysqld]
character_set_server=utf8
2、启动两个数据库实例
mysqld_multi --defaults-file=/etc/my_multi.cnf start
查看实例状态:
mysqld_multi --defaults-file=/etc/my_multi.cnf report
显示以下信息表示启动成功:
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
四、配置主从复制(异步)
1、配置主节点
登录主节点:mysql -uroot -S /tmp/mysql_3307.sock -hlocalhost -P3307
执行以下sql:
#创建用户
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
#授予复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 856 | | | |
+------------------+----------+--------------+------------------+-------------------+
2、配置从节点
登录从节点:mysql -uroot -S /tmp/mysql_3308.sock -hlocalhost -P3308
执行以下sql:
mysql> CHANGE MASTER TO
-> MASTER_HOST='localhost',
-> MASTER_PORT = 3307,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000005',
-> MASTER_LOG_POS=856;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
开启复制:start slave;
查看slave状态:show slave statusG;
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl2
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 2782
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 2949
Relay_Master_Log_File: mysql-bin.000006
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: 2782
Relay_Log_Space: 3155
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: 3307
Master_UUID: 621efc3e-ac4f-11ec-8fb4-d7f5457a9374
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.01 sec)
3、测试主从同步
主节点创建一个数据库:
mysql> create database my_db2;
Query OK, 1 row affected (0.01 sec)
从节点就会同步过去:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)