mysql主从复制、并行同步、半同步

复制源码编译的mysql到另一台服务器

[root@server1 local]# cd /usr/local/
[root@server1 local]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server1 local]# scp -rp mysql server2:/usr/local
[root@server1 init.d]# scp mysqld server2:/etc/init.d/
root@server2's password: 
mysqld                                        100%   10KB   8.1MB/s   00:00  
[root@server1 init.d]# scp /etc/my.cnf server2:/etc/
root@server2's password: 
my.cnf                                        100%  167   102.8KB/s   00:00   

 在server2中设置mysql

在server2中建立id一样的用户
[root@server2 ~]# groupadd -g 1001 mysql
[root@server2 ~]# useradd -u 1001 -g mysql -M -d /data/mysql -s /sbin/nologin mysql
[root@server2 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server2 ~]# ll /etc/init.d/mysqld ##要有执行权限
-rwxr-xr-x 1 root root 10566 Dec 15 11:03 /etc/init.d/mysqld
[root@server2 ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid



[root@server2 ~]# vim .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@server2 ~]# source .bash_profile

[root@server2 ~]# mkdir /data/mysql -p

[root@server2 ~]# chown mysql.mysql /data/mysql/

[root@server2 ~]# mysqld --initialize --user=mysql ##初始化之后，data/mysql里有文件
[root@server2 ~]# cd /data/mysql
[root@server2 mysql]# ls
auto.cnf         client-key.pem  ib_logfile1         private_key.pem  sys
ca-key.pem       ib_buffer_pool  mysql               public_key.pem
ca.pem           ibdata1         mysqld.log          server-cert.pem
client-cert.pem  ib_logfile0     performance_schema  server-key.pem

[root@server2 mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 

[root@server2 mysql]# cat mysqld.log

2021-12-15T03:13:48.634734Z 1 [Note] A temporary password is generated for root@localhost: >5*n4LyKfk16

[root@server2 mysql]# mysql_secure_installation 

[root@server2 mysql]# mysql -pwestos

一、主从复制

[root@server1 init.d]# vim /etc/my.cnf

log-bin=mysql-bin
server-id=1

[root@server1 init.d]# /etc/init.d/mysqld restart

主机mysql中建立一个用户
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

 

 

 从机可以全程登陆，代表权限设置成功
[root@server2 mysql]# mysql -h 172.25.6.1 -u repl -p

主机中
mysql> SHOW MASTER STATUS;

mysql> CREATE DATABASE westos;建立一个库

主从数据库应该保持一致，所以我们要先备份给2
[root@server1 mysql]# mysqldump -pwestos westos > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@server1 mysql]# vim dump.sql
[root@server1 mysql]# scp dump.sql server2:
root@server2's password: 
dump.sql                                      100% 1251     1.4MB/s   00:00