赞
踩
//在主数据库里创建一个同步账号授权给从数据库使用
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)
===================================================================
| 数据库角色 | 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 |
略
//在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)
略
略
[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)
===================================================================
=================================================================
| 数据库角色 | IP地址 | 应用与系统版本 |
| — | — | — |
| 主:master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 主:slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注Java)
| 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)
===================================================================
=================================================================
| 数据库角色 | IP地址 | 应用与系统版本 |
| — | — | — |
| 主:master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 主:slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
[外链图片转存中…(img-L6EzyFJY-1710785922665)]
[外链图片转存中…(img-NfZFqYfJ-1710785922666)]
[外链图片转存中…(img-1mpBimLp-1710785922666)]
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注Java)
[外链图片转存中…(img-GpkFV1th-1710785922666)]
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。