//建立数据存放目录
[root@master ~]# mkdir -p /opt/data/
[root@master ~]# chown -R mysql.mysql /opt/data/
[root@master ~]# ll /opt/data
总用量 0
//初始化数据库(不要密码)
[root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql
2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:33:24.529250Z 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: 57d51369-0a25-11ec-8346-000c2931864c.
2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
//安装依赖包
[root@master ~]# yum -y install ncurses-compat-libs
…
//生成配置文件
[root@master data]# vim /etc/my.cnf #如果这个文件存在,请先备份再修改
[root@master data]# cat /etc/my.cnf
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
skip-name-resolve #跳过名称解析
//配置服务启动脚本
[root@master ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@master ~]# vim /etc/init.d/mysqld #修改文件中的这两个地方
basedir=/usr/local/mysql
datadir=/opt/data
//启动mysql服务
[root@master ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master ~]# ss -antl|grep 3306
LISTEN 0 80 *:3306 :
//设置新密码
[root@master ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> set password = password(‘redhat123!’);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
slave:
//在master将mysql-5.7包传输到slave上
[root@master ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 192.168.129.135:/usr/src/
The authenticity of host ‘192.168.129.135 (192.168.129.135)’ can’t be established.
ECDSA key fingerprint is SHA256:rp+LMeFjAIapu3y+MH6bQa8QZpl/XRlUd1PammVI9E0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added ‘192.168.129.135’ (ECDSA) to the list of known hosts.
root@192.168.129.135’s password:
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 100% 635MB 102.6MB/s 00:06
//解压软件至/usr/local目录下,目录的位置可以是其他的地方
[root@slave ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave ~]# ls /usr/local/
apr bin games include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share
apr-util etc httpd lib libexec sbin src
//创建用户和组
[root@slave ~]# useradd -r -M -s /sbin/nologin mysql
//创建软连接
[root@slave ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 root root 46 8月 30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 8月 30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
//修改修改目录/usr/local/mysql的属主属组
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 mysql mysql 46 8月 30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql 129 8月 30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
//添加环境变量
[root@slave ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh
[root@slave ~]# source /etc/profile.d/mysql.sh
[root@slave ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
//建立数据存放目录
[root@slave ~]# mkdir -p /opt/data/
[root@slave ~]# chown -R mysql.mysql /opt/data/
[root@slave ~]# ll /opt/data
总用量 0
//初始化数据库(不要密码)
[root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql
2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:33:24.529250Z 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: 57d51369-0a25-11ec-8346-000c2931864c.
2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
//安装依赖包
[root@master ~]# yum -y install ncurses-compat-libs
…
//生成配置文件
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
skip-name-resolve
//配置服务启动脚本
[root@slave ~]# cp -a /usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
[root@slave ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
//启动mysql服务
[root@slave ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@slave ~]# ss -antl|grep 3306
LISTEN 0 80 *:3306 :
//设置新密码
[root@master ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> set password = password(‘redhat123!’);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
master:
[root@master ~]# systemctl disable --now firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config
[root@master ~]# setenforce 0
slave:
[root@slave ~]# systemctl disable --now firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config
[root@slave ~]# setenforce 0
//在主数据库里创建一个同步账号授权给从数据库使用
mysql> grant replication slave on . to ‘hhr’@‘192.168.129.135’ identified by ‘hhr123!’; #无需提前创建用户,执行授权的时候会自动创建用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@master ~]# vim /etc/my.cnf
server-id = 10 #主库的server_id必须小于从库
gtid-mode = on #开启gtid
enforce-gtid-consistency = on #强制gtid一致性
log-bin= mysql_bin #开启二进制
binlog-format = row #默认为mixed混合模式,更改成row复制,为了数据一致性
log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1 #跳过slave复制线程
//重启MySQL服务
[root@master ~]# service mysqld restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!
//检查gtid模式状态
mysql> show variables like ‘%gtid%’;
±---------------------------------±----------+
| Variable_name | Value |
±---------------------------------±----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
±---------------------------------±----------+
8 rows in set (0.00 sec)
[root@slave ~]# vim /etc/my.cnf
server-id = 20 #从库的server_id必须大于主库
gtid-mode = on #开启gtid
enforce-gtid-consistency = on #强制gtid一致性
log-bin = mysql_bin #开启二进制
binlog-format = row #默认为mixed混合模式,更改成row复制,为了数据一致性
log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1 #跳过slave复制线程
//重启MySQL服务
[root@slave ~]# service mysqld restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!
//检查gtid模式状态
mysql> show variables like ‘%gtid%’;
±---------------------------------±----------+
| Variable_name | Value |
±---------------------------------±----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
±---------------------------------±----------+
8 rows in set (0.00 sec)
//配置并启动主从复制
mysql> change master to
-> master_host=‘192.168.129.133’,
-> master_user=‘hhr’,
-> master_password=‘hhr123!’,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.129.133
Master_User: hhr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000003
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: 154
Relay_Log_Space: 574
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: 10
Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c
Master_Info_File: /opt/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:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
//主服务器创建数据库
mysql> create database hhr;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| hhr |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)
mysql> use hhr;
Database changed
mysql> create table student (id int not null primary key auto_increment,name varchar(50) not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> insert student (name,age) values(‘tom’,20),(‘jerry’,20),(‘wnagermazi’,15),(‘zhangsan’,19),(‘lisi’,25);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student;
±—±-----------±-----+
| id | name | age |
±—±-----------±-----+
| 1 | tom | 20 |
| 2 | jerry | 20 |
| 3 | wnagermazi | 15 |
| 4 | zhangsan | 19 |
| 5 | lisi | 25 |
±—±-----------±-----+
5 rows in set (0.01 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| mysql_bin.000003 | 870 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-3 |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
1 row in set (0.00 sec)
//查看从库中的数据是否同步成功
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| hhr |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.01 sec)
mysql> select * from hhr.student;
±—±-----------±-----+
| id | name | age |
±—±-----------±-----+
| 1 | tom | 20 |
| 2 | jerry | 20 |
| 3 | wnagermazi | 15 |
| 4 | zhangsan | 19 |
| 5 | lisi | 25 |
±—±-----------±-----+
5 rows in set (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| mysql_bin.000001 | 862 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-3 |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
1 row in set (0.00 sec)
停止
//从库停止服务
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
//主库
mysql> insert student (name,age) values(‘qq’,20),(‘ww’,20),(‘ee’,15),(‘rr’,19),(‘lisi’,25);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student;
±—±-----------±-----+
| id | name | age |
±—±-----------±-----+
| 1 | tom | 20 |
| 2 | jerry | 20 |
| 3 | wnagermazi | 15 |
| 4 | zhangsan | 19 |
| 5 | lisi | 25 |
| 6 | qq | 20 |
| 7 | ww | 20 |
| 8 | ee | 15 |
| 9 | rr | 19 |
| 10 | lisi | 25 |
±—±-----------±-----+
10 rows in set (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| mysql_bin.000003 | 1172 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-4 |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
1 row in set (0.00 sec)
//从库开启服务
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from hhr.student;
±—±-----------±-----+
| id | name | age |
±—±-----------±-----+
| 1 | tom | 20 |
| 2 | jerry | 20 |
| 3 | wnagermazi | 15 |
| 4 | zhangsan | 19 |
| 5 | lisi | 25 |
| 6 | qq | 20 |
| 7 | ww | 20 |
| 8 | ee | 15 |
| 9 | rr | 19 |
| 10 | lisi | 25 |
±—±-----------±-----+
10 rows in set (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| mysql_bin.000001 | 1156 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-4 |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
1 row in set (0.00 sec)
===================================================================
- 搭建三台MySQL服务器,一台作为主服务器,一台作为从服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
| 数据库角色 | IP地址 | 应用与系统版本 |
| — | — | — |
| 主:master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 从:slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
| 从:slave2 | 192.168.129.250 | Redhat 7/ MySQL 5.7 |
master
略
slave
//在slave将mysql-5.7包传输到slave2上
[root@slave ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 192.168.129.137:/usr/src/
The authenticity of host ‘192.168.129.137 (192.168.129.137)’ can’t be established.
ECDSA key fingerprint is SHA256:u29tZ8NW9v2uYAf2bAK7FOZwaxtjsxa0NVjgDTcaNTg.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added ‘192.168.129.137’ (ECDSA) to the list of known hosts.
root@192.168.129.137’s password:
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 100% 635MB 134.5MB/s 00:04
[root@slave2 ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave2 ~]# ls /usr/local/
bin games lib libexec sbin src
etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share
[root@slave2 ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave2 ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 root root 46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
[root@slave2 ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave2 ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql 46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
root@slave2 ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh
[root@slave2 ~]# source /etc/profile.d/mysql.sh
[root@slave2 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@slave2 ~]# mkdir -p /opt/data/
[root@slave2 ~]# chown -R mysql.mysql /opt/data/
[root@slave2 ~]# ll /opt/data/
total 0
[root@slave2 ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql
2021-08-31T09:15:31.711025Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T09:15:31.866033Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T09:15:31.889223Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T09:15:31.945429Z 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: fdd2fc8c-0a3b-11ec-82ba-000c29209bda.
2021-08-31T09:15:31.947661Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-08-31T09:15:32.598937Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T09:15:32.652206Z 1 [Note] A temporary password is generated for root@localhost: Qq73AjyWw2%/
[root@slave2 ~]# echo ‘Qq73AjyWw2%/’ > pass
//安装依赖包
[root@slave2 ~]# yum -y install ncurses-compat-libs
//位置文件
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
//配置服务启动脚本
[root@slave2 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@slave2 ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
//启动mysql服务
[root@slave2 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@slave2 ~]# ss -antl|grep 3306
LISTEN 0 80 *:3306 :
//设置新密码
[root@slave2 ~]# mysql -uroot -p’Qq73AjyWw2%/’
mysql> set password = password(‘redhat123!’);
Query OK, 0 rows affected, 1 warning (0.00 sec)
master、slave
略
略
[root@slave2 ~]# vim /etc/my.cnf
server-id = 21 #从库的server_id必须大于主库
gtid-mode = on #开启gtid
enforce-gtid-consistency = on #强制gtid一致性
log-bin = mysql_bin #开启二进制
binlog-format = row #默认为mixed混合模式,更改成row复制,为了数据一致性
log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1 #跳过slave复制线程
//重启MySQL服务
[root@slave2 ~]# service mysqld restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!
//检查gtid模式状态
mysql> show variables like ‘%gtid%’;
±---------------------------------±----------+
| Variable_name | Value |
±---------------------------------±----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
±---------------------------------±----------+
8 rows in set (0.01 sec)
//配置并启动主从复制
mysql> change master to
-> master_host=‘192.168.129.133’,
-> master_user=‘hhr’,
-> master_password=‘hhr123!’,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.129.133
Master_User: hhr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 1618
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 1831
Relay_Master_Log_File: mysql_bin.000003
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: 1618
Relay_Log_Space: 2039
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: 10
Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c
Master_Info_File: /opt/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: 57d51369-0a25-11ec-8346-000c2931864c:1-6
Executed_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
//从库查看
mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
| mysql_bin.000001 | 1602 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-6 |
±-----------------±---------±-------------±-----------------±-----------------------------------------+
1 row in set (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| hhr |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)
mysql> use hhr;
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> select * from student;
±—±-----------±-----+
| id | name | age |
±—±-----------±-----+
| 1 | tom | 20 |
| 2 | jerry | 20 |
| 3 | wnagermazi | 15 |
| 4 | zhangsan | 19 |
| 5 | lisi | 25 |
| 6 | qq | 20 |
| 7 | ww | 20 |
| 8 | ee | 15 |
| 9 | rr | 19 |
| 10 | lisi | 25 |
±—±-----------±-----+
10 rows in set (0.00 sec)
===================================================================
=================================================================
- 搭建三台MySQL服务器,两台作为主服务器,一台作为从服务器,从服务器进行读操作
| 数据库角色 | IP地址 | 应用与系统版本 |
| — | — | — |
| 主:master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 主:slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
| 从:slave2 | 192.168.129.250 | Redhat 7/ MySQL 5.7 |
//主库1设置:
mysql> grant replication slave on . to ‘hhr’@'192.168.129.250’identified by ‘hhr123!’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql_bin.000002 | 154 | | | |
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注:Java)
总结
谈到面试,其实说白了就是刷题刷题刷题,天天作死的刷。。。。。
为了准备这个“金三银四”的春招,狂刷一个月的题,狂补超多的漏洞知识,像这次美团面试问的算法、数据库、Redis、设计模式等这些题目都是我刷到过的
并且我也将自己刷的题全部整理成了PDF或者Word文档(含详细答案解析)
66个Java面试知识点
架构专题(MySQL,Java,Redis,线程,并发,设计模式,Nginx,Linux,框架,微服务等)+大厂面试题详解(百度,阿里,腾讯,华为,迅雷,网易,中兴,北京中软等)
算法刷题(PDF)
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
s affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql_bin.000002 | 154 | | | |
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。[外链图片转存中…(img-Dmn8RJ0a-1713852463887)]
[外链图片转存中…(img-Lf8ifolK-1713852463888)]
[外链图片转存中…(img-HPsuHaDq-1713852463888)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注:Java)
[外链图片转存中…(img-Og6Pa2ca-1713852463888)]
总结
谈到面试,其实说白了就是刷题刷题刷题,天天作死的刷。。。。。
为了准备这个“金三银四”的春招,狂刷一个月的题,狂补超多的漏洞知识,像这次美团面试问的算法、数据库、Redis、设计模式等这些题目都是我刷到过的
并且我也将自己刷的题全部整理成了PDF或者Word文档(含详细答案解析)
[外链图片转存中…(img-nb8nFlqf-1713852463888)]
66个Java面试知识点
架构专题(MySQL,Java,Redis,线程,并发,设计模式,Nginx,Linux,框架,微服务等)+大厂面试题详解(百度,阿里,腾讯,华为,迅雷,网易,中兴,北京中软等)
[外链图片转存中…(img-nhVJB6gY-1713852463889)]
算法刷题(PDF)
[外链图片转存中…(img-SJKzcwqN-1713852463889)]
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!