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

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

[root@server1 local]# cd /usr/local/
[root@server1 local]# scp -rp mysql server2:/usr/local/  
[root@server1 local]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server1 local]# cd /etc/init.d/
[root@server1 init.d]# ls
functions  mysqld  netconsole  network  README  rhnsd
[root@server1 init.d]# scp mysqld server2:/etc/init.d/
root@server2's password: 
mysqld                                       100%   10KB   7.0MB/s   00:00    
[root@server1 init.d]# scp /etc/my.cnf server2:/etc/
root@server2's password: 
my.cnf                                       100%  168   109.0KB/s   00:00    

在server2中设置mysql

[root@server2 ~]# cat /etc/group   
[root@server2 ~]# groupadd -g 1001 mysql   ###在server2中建立id一样的用户

[root@server2 ~]# useradd -u 1001 -g mysql -M -d /data/mysql -s /sbin/noligin 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:05 /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   ##编辑文件，加入路径：/usr/local/mysql/bin
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# User specific environment and startup programs

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

export PATH
[root@server2 ~]# source .bash_profile
[root@server2 ~]# mkdir /data/mysql -p   ###建立data目录
[root@server2 ~]# chown mysql.mysql /data/mysql/    ##修改权限
[root@server2 ~]# mysqld --initialize --user=mysql   ##初始化
[root@server2 ~]# cd /data/mysql
[root@server2 mysql]# ls

[root@server2 mysql]# cat mysqld.log  ##在日志中查看生成的随机密码

2021-12-15T03:15:51.241040Z 1 [Note] A temporary password is generated for root@localhost: 30H/0I#iDZ7M
[root@server2 mysql]# /etc/init.d/mysqld start  ##启动
Starting MySQL. SUCCESS! 
[root@server2 mysql]# mysql_secure_installation ##设置超户密码和登陆权限
[root@server2 mysql]# mysql -pwestos   ##新密码登陆（测试正常后退出）

 

 

 一.主从复制

[root@server1 init.d]# vim /etc/my.cnf   ##编辑配置文件（加入log-bin和server-id）
[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

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

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

主机mysql中建立一个用户：
[root@server1 mysql]# mysql -pwestos
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';     ##设置westos库的用户和权限

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

 

 

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

 主机中：

主机中：
mysql> SHOW MASTER STATUS;
mysql> CREATE DATABASE westos;建立一个库
ERROR 1007 (HY000): Can't create database 'westos'; database exists
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.01 sec)

mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> quit
Bye
[root@server1 mysql]# cd
[root@