ubuntu系统下的mysql主从

流程：
1.使登陆用户为root
2.接受远程连接
3.mysql主从搭建

mysql主从搭建流程：
1.修改主配置文件（从服务器上相同操作））
2.master授权给slave
3.salve配置，与master保持连接 （连接前，先停掉slave）
4.master优化或在线优化
5.启动mysql并行复制
6.增加多线程间同步开销
7.并行复制配置于调优

######## 流程：
1.使登陆用户为root
/usr/share/lightdm/lightdm.conf.d/50-ubuntu.conf
···
[SeatDefaults]
autologin-user=root ##root用户登陆
user-session=ubuntu ##用户会话
greeter-show-manual-login=true ##手工输入登陆系统和用户名密码
····

2.接受远程登陆
/root/.profile
···
tty -s && mesg n || true ##自动登陆
···

######## mysql主从搭建流程：
1.修改主配置文件（salve上类似）
/etc/my.cnf
···
log-bin = mysql-bin ##将binlog文件取名为mysql-bin
binlog_format =mixed ##binlog文件格式
server_id = 131 ##独立的id
···
重启mysql服务，使其生效

2.master授权给slave
mysql>
....
mysql>GRANT replication slave ON . TO 'slave'@'%' IDENTIFIED BY '111111';
mysql>FLUSH PRIVILEGES;
....

3.salve配置，与master保持连接 （连接前，先停掉slave）
a.mysql>stop slave;(salve操作)
mysql>show master status;(master操作)
mysql>CHANGE MASTER TO （slave与master建立连接）
MASTER_HOST ="10.0.0.128",
MASTER_USER ="slave",
MASTER_PASSWORD ="111111",
MASTER_LOG_FILE ="mysql-bin.000001",
MASTER_LOG_POS = 590;
mysql>start slave; （slave操作）
b.查看配置是否成功
mysql> show slave status;
···
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
···

4.master优化或在线优化
a.优化
/ect/my.cnf
[mysqld]
···
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
···
b.在线优化
mysql>stop slave;
mysql>set global slave_parallel_type='LOGICAL_CLOCK';
mysql>set global slave_parallel_workers=4;
mysql>start slave;
mysql> show variables like 'slave_parallel_%';

5.启动mysql并行复制（master+slave）
mysql> show global variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+

6.增加多线程间同步开销（slave）
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> set global slave_parallel_type='LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)
mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
mysql> show variables like 'slave_parallel_%';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
+------------------------+---------------+

7.并行复制配置于调优
a./etc/my.cnf
[mysqld]
···
master-info-repository = table
relay-log-info-repository = table
relay-log-recovery = ON
···
b.service mysql restart ##重启mysql服务
c.并行复制监控
mysql> use performance_schema;
mysql> show tables like 'replication%';
+---------------------------------------------+
| Tables_in_performance_schema (replication%) |
+---------------------------------------------+
| replication_applier_configuration |
| replication_applier_status |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker |
| replication_connection_configuration |
| replication_connection_status |
| replication_group_member_stats |
| replication_group_members |
+---------------------------------------------+
8 rows in set (0.00 sec)