这一篇主要来测评一下在云芯1号上搭建MySQL集群。设计数据库集群的需求主要是出于数据库可靠性和性能的考虑。由于单一的数据库服务器风险较高,一旦单一服务器出现问题,整个数据库服务就无法使用;另外一方面随着业务需求量的增加,单一数据库服务器也难以满足。
MySQL有多种集群方案,这篇测评里我们根据评测教程,试验了Replication主从架构集群方案。
1.配置MySQL多实例
最基本的Replication主从架构需要一主一从两台服务器。我们在单个设备上配置多实例来启动两个MySQL实例,来配置一主一从的Replication架构
1.1创建多实例数据
mkdir -p mysql/3306/data
mkdir -p mysql/3306/data
sudo chown -R mysql:mysql mysql
1.2初始化多实例数据库
sudo mysqld --initialize-insecure --basedir=/usr --datadir=/home/jishu/mysql/3306/data/ --user=mysql
sudo mysqld --initialize-insecure --basedir=/usr --datadir=/home/jishu/mysql/3307/data/ --user=mysql
可以看到初始化数据库所创建的文件
1.3创建多实例配置文件
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = 123456
log = /home/jishu/mysql/mysqld_multi.log
[mysqld1]
port = 3306
socket = /home/jishu/mysql/3306/mysql.sock
pid_file = /home/jishu/mysql/3306/mysql.pid
basedir = /usr
datadir = /home/jishu/mysql/3306/data/
# LOGGING
long_query_time = 5
slow_query_log = 1
log_error = /home/jishu/mysql/3306/error.log
slow_query_log_file = /home/jishu/mysql/3306/slow_query.log
log_warnings = 1
# INNODB
innodb_data_home_dir = /home/jishu/mysql/3306/data
innodb_buffer_pool_size = 64M
innodb_log_file_size = 64M
innodb_data_file_path = ibdata1:12M:autoextend:max:64M
# REPLICATION
server-id = 3306
log-bin = /home/jishu/mysql/3306/mysql-binlog
binlog-do-db = musics
[mysqld2]
port = 3307
socket = /home/jishu/mysql/3307/mysql.sock
pid_file = /home/jishu/mysql/3307/mysql.pid
basedir = /usr
datadir = /home/jishu/mysql/3307/data/
# LOGGING
long_query_time = 5
slow_query_log = 1
log_error = /home/jishu/mysql/3307/error.log
slow_query_log_file = /home/jishu/mysql/3307/slow_query.log
log_warnings = 1
# INNODB
innodb_data_home_dir = /home/jishu/mysql/3307/data
innodb_buffer_pool_size = 64M
innodb_log_file_size = 64M
innodb_data_file_path = ibdata1:12M:autoextend:max:64M
# REPLICATION
server-id = 3307
log-bin = /home/jishu/mysql/3307/mysql-binlog
1.4启动多实例
先杀掉mysql服务进程
sudo mysqld_multi --defaults-file=./my.cnf start
多实例启动后,用lsof命令看看3306和3307端口的服务器是否已经启动
2.配置Replication主从架构
2.1连接数据库实例的用户
sudo mysqladmin -u root password 123456 -S ./3306/mysql.sock
sudo mysqladmin -u root password 123456 -S ./3307/mysql.sock
2.2创建Replication复制账号
mysql -uroot -p123456 -S ./3306/mysql.sock
mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;
2.3创建执行数据库
mysql> create database musics;
mysql> show databases;
2.4配置主从Replication
mysql> show master status\G;
先锁定
mysql> use musics;
mysql> flush tables with read lock;
把3307端口的作为从数据库
mysql -uroot -p123456 -S 3307/mysql.sock
mysql> stop slave;
mysql> change master to master_host='127.0.0.1',master_user='backup',master_password='123456',master_port=3306,master_log_file='mysql-binlog.000001',master_log_pos=1279;
mysql> start slave;
注意这里的master_log_file和master_log_pos一定要正确指定,可以通过查询主数据库的状态得到。
查看从数据库状态
mysql> show slave status\G;
slave状态查询结果中,如果Slave-IO-Running和Slave-SQL-Running的状态都是Yes时,表示主从数据库的Replication正常。
Replication运行正常后,要把主数据库解锁
mysql> unlock tables;
3.Replication测试
连接主数据库,创建表格
mysql> create table tbl_songs(id int, name varchar(16));
mysql> create table tbl_albums(id int, name varchar(32));
连接从数据库,查看表格
mysql> show databases;
mysql> use musics;
mysql> show tables;
数据库和两张表都已经同步,Replication功能正常。
如果没有正常同步拉到musics数据库,可以尝试以下步骤:
参考资料
- 云芯一号教程 - MySQL集群搭建教程,https://aijishu.com/a/1060000...