当单台MYSQL服务器无法满足当前网站流量时的优化方案。需要搭建mysql集群技术。
复制方式:(同步方式)
- 主–从复制
- 主–主复制
- 半同步复制
复制原理:
Mysql中有一种日志叫做bin日志(二进制日志)。这个日志会记录下所有修改了数据库的SQL语句(insert,update,delete,ALTER TABLE,grant等等)。
主从复制的原理其实就是把主服务器上的BIN日志复制到从服务器上执行一遍,这样从服务器上的数据就和主服务器上的数据相同了。
异步复制:
在主节点上执行完命令之后生成一个日志文件,然后将日志文件发送给从节点,最后提交,从节点收到日志文件后,开始同步里面的数据(事物),最后得到与主节点一致的数据
效果图如下
半同步复制:
而半同步复制是在协议中添加了一个同步步骤,主节点在发送完日志文件之后,等待来自从节点的回应,收到从节点的同步确认之后才去提交数据(保证数据一定被备份了)
一般主从复制,有三个线程参与,都是单线程:Binlog Dump(主) —–>IO Thread (从) —–> SQL Thread(从)。
复制只能是单向的,从主节点(master)到从节点(slave)上.
master用户写入数据,生成event记录到binary-log(二进制日志)中.
slave接收master上传来的binlog,然后按顺序应用,重现master上的用户操作.
安装mysql:
主数据库配置:
server-id = 1 #这是数据库ID,此ID是唯一的,主库默认为1,其他从库以此ID进行递增,ID值不能重复,否则会同步出错;
log-bin = mysql-bin 二进制日志文件,此项为必填项,否则不能同步数据;
配置完成后需要建立同步用的数据库账户
主库需要提供一个账户让从库对起连接并同步,用命令mysql服务器
mysql>grant replication slave on *.* to repl@'172.25.5.%' identified by 'sH+101010';
说明:repl:同步的数据库;
172.25.5.3:同步的数据库地址;
sH+101010:同步的数据库密码,在从库配置时需要设置。
显示主库信息
mysql>show master status;
执行上述命令显示:
看到上述信息则表示主库建立成功。
从数据库配置
从服务器的配置基本与主库配置差不多,选择的从库服务器为:172.25.5.4
- 配置从库服务my.cnf文件
- vietc/my.cnf 对其进行如下修改:
server-id = 2 #这里ID改为2 因为主库为1;
- 调整复制过来的数据库的权限(如果有从主库复制过来的数据库)
- 主库复制过来的数据库权限都为root权限,需要修改为mysql权限
- chown–R mysql:mysql /var/lib/mysql
- 如果从库是新建的库就不需要进行此步骤
mysql>showslave status\G; #查看服务器状态
mysql> change master to master_host='172.25.5.3', master_user='repl', master_password='sH+101010',master_log_file='mysql-bin.000003', master_log_pos=2045;
Query OK, 0 rows affected, 2 warnings (0.97 sec)
mysql> start slave;
mysql>Change master to master_host = '172.25.5.%',
master_user= 'repl',
master_password='sH+101010',
master_log_file='mysql-bin.000003',
master_log_pos='2045';
如果slave_io_running和slave_sql_running都为yes则表示从服务器配置成功。
如果遇到关于position相关的错误,就需要再手动设置以下File/Postion的信息
gdit
配置文件:(加入)
gtid_mode=ON
enforce-gtid-consistency=true
加入gtid后,可以这样改变master:
mysql> change master to master_host='172.25.5.3', master_user='repl', master_password='sH+101010', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.40 sec)
mysql>
slave端:
半同步复制配置
mysql默认异步复制,在设置成半同步之后,如果节点出了问题(master收不到slave的ack返回值),一定的等待时间之后,系统就会自动切换回异步复制.
配置
主节点(master):
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.09 sec)
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.02 sec)
mysql>
show plugins # 查看插件。
mysql> show plugins
-> ;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
从节点(slave):
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.06 sec)
mysql> set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.03 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql>
插入检测:
mysql> insert into stulist.list value ('user3','666');
Query OK, 1 row affected (0.08 sec)
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 519 |
| Rpl_semi_sync_master_tx_wait_time | 519 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql>
将slave的半同步关掉,进行插入测试:
slave:
mysql> set global rpl_semi_sync_slave_enabled=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql>
master:
mysql> insert into stulist.list value ('user4','888');
Query OK, 1 row affected (10.08 sec)
#本次插入花费了十秒,因为异步复制有十秒等待slave发送ack的时间.
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 519 |
| Rpl_semi_sync_master_tx_wait_time | 519 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql>