Mysql5.7搭建主从复制
|
458
|
0
|
MySQL

894 字
|
22 分钟

Mysql Replication

一、环境

Role HostName IP Version Port Server_id Database Basedir Datadir
Master server01 192.168.1.191 Mysql 5.7 3307 1 mydb /usr/local/mysql /data/mysql/data
Slave server02 192.168.1.192 Mysql 5.7 3308 2 mydb /usr/local/mysql /data/mysql/data

二、安装Master数据库

1.关闭防火墙

配置主从需关闭防火墙或开通3307 3308端口

关闭防火墙:
[root@server02 ~]# systemctl status firewalld.service
[root@server02 ~]# systemctl stop firewalld.service
[root@server02 ~]# systemctl disable firewalld.service

开通端口:
[root@server01 ~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent
[root@server01 ~]# firewall-cmd --reload
1.配置YUM
[root@mysql opt]# cp -r /run/media/admin/CentOS\ 7\ x86_64/*  /opt/yum

[root@mysql opt]# vi /etc/yum.repos.d/CentOS-Base.repo

[CentOS-Base]
baseurl=file:///opt/yum
gpgcheck=0
enabled=1
2.安装操作系统依赖包
For MySQL 5.7.19 and later: Support for Non-Uniform Memory Access
(NUMA) has been added to the generic Linux build, which has a dependency
now on the libnuma library; if the library has not been installed on your
system, use you system's package manager to search for and install it (see
the preceding item for some sample commands).

SLES 11: As of MySQL 5.7.19, the Linux Generic tarball package format is
EL6 instead of EL5. As a side effect, the MySQL client bin/mysql needs
libtinfo.so.5.
A workaround is to create a symlink, such as

ln -s libncurses.so.5.6 /lib64/libtinfo.so.5      on 64-bit systems   or
ln-s libncurses.so.5.6 /lib/libtinfo.so.5         on 32-bit systems.

[root@mysql soft]# yum install libaio
[root@mysql soft]# yum install libnuma
3.配置数据磁盘
磁盘挂载:

[root@mysql soft]# mkfs.xfs  /dev/sdb
meta-data=/dev/sdb               isize=512    agcount=4, agsize=1310720 blks
      =                       sectsz=512   attr=2, projid32bit=1
      =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=5242880, imaxpct=25
      =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
      =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

[root@mysql soft]# blkid
/dev/sda1: UUID="28c5912e-d7ae-4c5f-a404-f1806d48ca93" TYPE="xfs"
/dev/sda2: UUID="VczXPR-tZhP-TR58-NYzW-3hsR-F3Qc-TzTaO6" TYPE="LVM2_member"
/dev/sr0: UUID="2020-11-04-11-36-43-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos"
/dev/sdb: UUID="804f4903-7d62-4669-b48a-5add1d0d2312" TYPE="xfs"
/dev/mapper/centos-root: UUID="6947bf69-372b-4127-b1c9-287c133c0090" TYPE="xfs"

[root@mysql soft]# vi /etc/fstab

UUID="804f4903-7d62-4669-b48a-5add1d0d2312"  /data    xfs   defaults        0 0

[root@mysql soft]# mount -a
4.卸载Mariadb
[root@mysql mysql]# rpm -qa |grep mariadb
[root@mysql mysql]# yum remove mariadb-libs-5.5.68-1.el7.x86_64
5.创建用户和组
[root@server01 ~]# groupadd -g 600 mysql

[root@server01 ~]# useradd -r -g mysql -u 600 -s /bin/false mysql

温馨提示: -r参数指的是创建系统用户，该用户没有登录权限。

[root@mysql ~]# id mysql
uid=600(mysql) gid=600(mysql) groups=600(mysql)
6.解压安装包
[root@server02 soft]# tar zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

温馨提示:如果tar不支持z选项，则可以使用gunzip命令

gunzip < mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz |tar xvf -
7.创建软件链接
[root@mysql soft]# cd /usr/local/

[root@server02 local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql
8.添加PATH环境变量
[root@mysql mysql]# vim /etc/profile

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

温馨提示: 也可以使用sed命令添加

[root@mysql mysql]# sed -i '$a export PATH=/usr/local/mysql/bin:$PATH\n' /etc/profile

[root@mysql mysql]# source /etc/profile

[root@mysql mysql]# tail -2 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
9.创建数据文件目录
[root@mysql soft]# cd /usr/local/mysql

[root@mysql mysql]# mkdir mysql-files

[root@mysql mysql]# chown mysql:mysql mysql-files
[root@mysql mysql]# chmod 750 mysql-files
10.初如化目录

--initialize生成一个临时密码:

  • 密码12位数字，4种字符组合
  • 密码有效期180天
  • 如果不想使用这种密码复杂策略，可以使用--initialize-insecure
如果初始化报如下错误:

error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

则安装以下包即可:
[root@mysql mysql]# yum install libaio-devel

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

[root@mysql ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data --explicit_defaults_for_timestamp
2021-07-14T15:04:08.596953Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-14T15:04:08.617238Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-14T15:04:08.684478Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bd581429-e4b4-11eb-8cfb-000c29b39c96.
2021-07-14T15:04:08.686754Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-14T15:04:08.689282Z 1 [Note] A temporary password is generated for root@localhost: JY!btpV5tpt_

[root@mysql ~]# ll /data/mysql/data/
total 110628
-rw-r-----. 1 mysql mysql       56 Jul 14 23:04 auto.cnf
-rw-r-----. 1 mysql mysql      419 Jul 14 23:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 14 23:04 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 14 23:04 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 14 23:04 ib_logfile1
drwxr-x---. 2 mysql mysql     4096 Jul 14 23:04 mysql
drwxr-x---. 2 mysql mysql     8192 Jul 14 23:04 performance_schema
drwxr-x---. 2 mysql mysql     8192 Jul 14 23:04 sys
11.配置SSL支持
[root@db01 ~]# mysql_ssl_rsa_setup --datadir=/data/mysql/data

Generating a 2048 bit RSA private key
......................................................................+++
......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.............................+++
..........+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
...........................+++
...+++
writing new private key to 'client-key.pem'
-----

分配权限：
[root@db01 ~]# cd /data/mysql/data
[root@db01 data]# chown mysql:mysql *.pem
12.配置my.cnf参数文件
Mysql my.cnf：

[root@server01 mysql]# cat > /etc/my.cnf <<EOF

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=1
port=3307

[mysql]
socket=/tmp/mysql.sock

EOF
13.配置启动文件

两种启动方式:

  • sys-v
cp support-files/mysql.server /etc/init.d/mysqld

/etc/init.d/mysqld start

service mysqld restart
  • systemd
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Service]
User=mysql
Group=mysql
ExecStart= /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ExecReload= /etc/init.d/mysqld restart
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

EOF

1，创建启动文件 .service
vim /usr/lib/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Service]
User=mysql
Group=mysql
Type=forking
PermissionsStartOnly=true
ExecStart= /etc/init.d/mysqld start
ExecStop= /etc/init.d/mysqld stop
ExecReload= /etc/init.d/mysqld restart
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

2，重新加载一下服务的配置文件
systemctl daemon-reload
3，开启mysql服务
systemctl  start  mysqld.service
systemctl  start  mysqld
4，关闭mysql服务
systemctl  stop  mysqld.service
systemctl  stop  mysqld
14.Mysql启动

| 启动命令                   | 关闭命令                      |
| -------------------------- | ----------------------------- |
| mysqld --user=mysql &      | mysqladmin -uroot -p shutdown |
| mysqld_safe --user=mysql & | mysqladmin -uroot -p shutdown |
| /etc/init.d/mysql start    | /etc/init.d/mysql stop        |

[root@mysql ~]# mysqld_safe --user=mysql &

判断Mysql是否启动:
netstat -lnp |grep 3307
ps -ef |grep mysql
lsof -i:3307
15.创建root密码
[root@mysql ~]# mysqladmin -uroot -p password '123456'
16.删除test库相关信息
[root@mysql ~]# mysql_secure_installation

三、配置Master

1.开启二进制
[root@server01 ~]# vi /etc/my.cnf

log_bin=mysql-bin
2.配置server_id
[root@server01 ~]# vi /etc/my.cnf

server_id=1
3.开启GTID

此步不是必须，按实际环境配置

[root@server01 ~]# vi /etc/my.cnf

gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
4.创建复制用户
mysql> create user repl@'192.168.1.%' identified by '123';

mysql> grant replication slave on *.* to repl@'192.168.1.%';
5.配置字符集

此步不是必须，按实际环境配置

[root@server01 ~]# vi /etc/my.cnf

[mysqld]

character-set-server=utf8mb4
6.Master创建数据

此步不是必须，按实际环境配置

mysql> create database mydb char set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb
Database changed

mysql> create table t (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (id int, name varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (1,'a');
Query OK, 1 row affected (0.00 sec)
7.重启主库
[root@server01 ~]# mysqladmin -uroot -p shutdown

[root@server01 ~]# mysqld_safe --user=mysql &
8.备份主库
[root@server01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
或
[root@server01 ~]# innobackupex -uroot -p123456 /tmp/xtrabackup/full

四、配置Slave

1.安装Slave数据库

参考Master

2.开启二进制
[root@server01 ~]# vi /etc/my.cnf

log_bin=mysql-bin
3.配置server_id
[root@server01 ~]# vi /etc/my.cnf

server_id=2
4.开启GTID

此步不是必须，按实际环境配置

[root@server01 ~]# vi /etc/my.cnf

gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
5.配置字符集

此步不是必须，按实际环境配置

[root@server01 ~]# vi /etc/my.cnf

[mysqld]

character-set-server=utf8mb4
6.重启从库
[root@server01 ~]# mysqladmin -uroot -p shutdown

[root@server01 ~]# mysqld_safe --user=mysql &
7.主库备份还原从库
[root@server02 mysql]# scp 192.168.1.191:/tmp/full.sql /tmp/

mysql> source /tmp/full.sql
8.确认备份点
[root@server02 ~]# more /tmp/full.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
9.创建同步进程
mysql> help change master to

CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

按实际环境修改:
CHANGE MASTER TO
  MASTER_HOST='192.168.1.191',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.1.191',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123',
    ->   MASTER_PORT=3307,
    ->   MASTER_LOG_FILE='mysql-bin.000003',
    ->   MASTER_LOG_POS=154,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
10.从库开启同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
11.查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.191
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 194
               Relay_Log_File: server02-relay-bin.000014
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000007
             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: 194
              Relay_Log_Space: 704
              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
                  Master_UUID: 954880d4-e57a-11eb-b6a9-000c29b39c96
             Master_Info_File: /data/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 954880d4-e57a-11eb-b6a9-000c29b39c96:1-4
            Executed_Gtid_Set: 954880d4-e57a-11eb-b6a9-000c29b39c96:1-4
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇