扩展：
scale on：向上扩展，垂直扩展
scale out：向外扩展，水平扩展
MySQL保存二进制日志：
statement
row
mixed
默认为异步工作模式
SLAVE:
IO thread：向主服务请求二进制日志中的事件
SQL thread：从中继日志读取事件并在本地执行
MASTER：
binlog dump:将IO thread请求的事件发送给对方：
工作架构：
从服务器：有且只能有一个主服务器：
MariaDB-10: 支持多主模型，多源复制（multi-source replication）
一主多从：
读写分离：主从模型下，让前端分发器能识别读/写，并且按需调度至目标主机：
amoeba：
mysql-proxy：
双主模型：
1、必须设定双方的自动增长属性，以避免冲突
auto_increment_increment=#
定义自动增长的起始值
auto_increment_offset=2
步长
2、数据不一致
功能：均衡读请求： 写请求双方一致：
示例：
主从复制的配置
版本
1、双方的MySQL版本要一致：
2、如果不一致，主的要低于从的：
从哪儿进行复制：
1、都从开始 ：
2、主服务器已经运行一段时间，并且存在不小的数据集：
把主服务器备份，然后在从服务器恢复,从主服务器上备份时所处的位置开始复制：
配置过程：
主服务器：
1、 改server-id
2、启用二进制日志
3、创建有复制权限的帐号
从服务器：
1、改server-id
2、启用中继日志
3、连接主服务器
4、启动复制线程
连接主服务器：
CHANGE MASTER TO
MASTER_HOST = '', MASTER_USER='',MASTER_PASSWORD='',MASTER_LOG_FILE='',MASTER_LOG_POS='';
实验：两数据库主（192.168.0.108），从数据库（192.168.0.111）
一、数据库做主从模型
1、主从服务器修改配置
主数据库：
（1）、修改主配置文件，在主配置文件里加入下面所示的几项，如已经有了，可不加
vim /etc/my.cnf
log_bin=mysql-bin
binlog_format=mixed
server-id=1
（2）、启动mysql服务
[root@localhost ~]# systemctl start mariadb.service
（3）、创建复制用户，并创建密码和赋予相应的权限
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)
（4）、刷新授权表
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
从数据库：
（1）、修改主配置文件,把源二进制日志注释掉
vim /etc/my.cnf
#log_bin=mysql-bin
加入下面的两项
server-id=2
relay-log=/var/log/mariadb/relay-bin
（2）、启动mysql服务
[root@localhost ~]# systemctl start mariadb.service
（3）、连到数据库上查看relay-log文件的启动情况，可以看到relay-log已经启动
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%relay%';
+----------------------------------+----------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------+
| innodb_recovery_update_relay_log | OFF |
| max_relay_log_size | 0 |
| relay_log | /var/log/mariadb/relay-bin |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+----------------------------------+----------------------------+
10 rows in set (0.00 sec)
2、把从数据库指向主数据库
（1）、查看下CHANGE MASTER TO 命令的帮助
从数据库：
MariaDB [(none)]> HELP CHANGE MASTER TO
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
（2）、先查看主数据库二进制日志的信息
主数据库：
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 497 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
（3）、把从数据库指向主数据库
从数据库：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.108',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=497;
Query OK, 0 rows affected (0.06 sec)
（4）、主数据库查看启动的进程和二进制日志信息
主数据库：
MariaDB [(none)]> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | NULL | Sleep | 2509 | | NULL | 0.000 |
| 3 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
2 rows in set (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 497 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
（5）、从数据库查看一些与主从相关的命令
从数据库：
MariaDB [(none)]> HELP START SLAVE;
Name: 'START SLAVE'
Description:
Syntax:
START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_types:
[thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD
（6）、启动主从同步
从数据库：
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.07 sec)
（7）、查看主从同步的启动日志
从数据库：
查看主从同步的启动日志：
[root@localhost ~]# tail -f /var/log/mariadb/mariadb.log
170330 9:54:59 InnoDB: Waiting for the background threads to start
170330 9:55:00 Percona XtraDB () 5.5.40-MariaDB-36.1 started; log sequence number 1612230
170330 9:55:00 [Note] Plugin 'FEEDBACK' is disabled.
170330 9:55:00 [Note] Server socket created on IP: '0.0.0.0'.
170330 9:55:00 [Note] Event Scheduler: Loaded 0 events
170330 9:55:00 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.41-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
170330 10:23:00 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.0.108', master_port='3306', master_log_file='mysql-bin.000002', master_log_pos='497'.
170330 10:34:36 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 497, relay log '/var/log/mariadb/relay-bin.000001' position: 4
170330 10:34:36 [Note] Slave I/O thread: connected to master 'repluser@192.168.0.108:3306',replication started in log 'mysql-bin.000002' at position 497
（8）、查看从数据库主从同步的状态，可以看到主从同步的进程已经启动
从数据库：
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.108
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 497
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
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: 497
Relay_Log_Space: 817
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: 1
1 row in set (0.01 sec)
（9）、做主从同步的测试
主数据库:
创建个mydb的数据库，
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.04 sec)
查看二进制日志的名称和位置状态
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 580 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
从数据库：
查看主从同步状态，可以看到同步的二进制日志名称和位置都已发生里变化，二进制日志已实现主从同步
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.108
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 580
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 612
Relay_Master_Log_File: mysql-bin.000002
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: 580
Relay_Log_Space: 900
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: 1
查看主数据库创建的数据库，已同步过来
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.05 sec)
注意：SlAVE 进程是随数据库启动而启动的，一般打开数据库后不用手动启动
阅读(1187) | 评论(0) | 转发(0) |
