找段子的各大网站,wordpress迁移到thinkphp,给个网址2021年能看的,佛山建网站定制费用mysql主从 文章目录 mysql主从1. 主从简介1.1 主从作用1.2 主从形式 2. 主从复制原理3. 主从复制配置3.1 mysql安装#xff08;两台主机安装一致#xff0c;下面只演示一台主机操作#xff09;3.2 mysql主从配置3.2.1 确保从数据库与主数据库里的数据一样3.2.2 在主数据库里…mysql主从 文章目录 mysql主从1. 主从简介1.1 主从作用1.2 主从形式 2. 主从复制原理3. 主从复制配置3.1 mysql安装两台主机安装一致下面只演示一台主机操作3.2 mysql主从配置3.2.1 确保从数据库与主数据库里的数据一样3.2.2 在主数据库里创建一个同步账号授权给从数据库使用3.2.3 配置主数据库3.2.4 配置从数据库3.2.5 测试验证 1. 主从简介
在现代企业中数据显得尤为重要而存储数据的数据库选择又五花八门但无论是何种数据库均存在着一种隐患。
想几个问题
用一台数据库存放数据若此数据库服务器宕机了导致数据丢失怎么办业务量大了数据多了访问的人多了一台数据库无法保证服务质量了怎么办
1.1 主从作用
实时灾备用于故障切换读写分离提供查询服务备份避免影响业务
1.2 主从形式 一主一从主主复制一主多从—扩展系统读取的性能因为读是在从库读取的多主一从—5.7开始支持联级复制
2. 主从复制原理 主从复制步骤
主库将所有的写操作记录到binlog日志中并生成一个log dump线程将binlog日志传给从库的I/O线程从库生成两个线程一个I/O线程一个SQL线程 I/O线程去请求主库的binlog并将得到的binlog日志写到relay log中继日志 文件中SQL线程会读取relay log文件中的日志并解析成具体操作来实现主从的操作一致达到最终数据一致的目的
3. 主从复制配置
主从复制配置步骤
确保从数据库与主数据库里的数据一样在主数据库里创建一个同步账号授权给从数据库使用配置主数据库修改配置文件配置从数据库修改配置文件
需求 搭建两台MySQL服务器一台作为主服务器一台作为从服务器主服务器进行写操作从服务器进行读操作
环境说明
数据库角色IP应用与系统版本有无数据主数据库192.168.116.140rockylinux9无数据从数据库192.168.116.143rockylinux9无数据
3.1 mysql安装两台主机安装一致下面只演示一台主机操作
分别在主从两台服务器上安装mysql8.0.35版本此处略过安装步骤若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。
1.修改主机名
[rootlocalhost ~]# hostnamectl set-hostname master
[rootlocalhost ~]# bash
[rootmaster ~]# [rootlocalhost ~]# hostnamectl set-hostname slave
[rootlocalhost ~]# bash
[rootslave ~]# 2.关闭防火墙
[rootmaster ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[rootmaster ~]# vi /etc/selinux/config
[rootmaster ~]# cat /etc/selinux/config|grep ^SELINUX
SELINUXdisabled
SELINUXTYPEtargeted
[rootmaster ~]# setenforce 0
[rootmaster ~]# getenforce
Permissive[rootslave ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[rootslave ~]# vi /etc/selinux/config
[rootslave ~]# cat /etc/selinux/config|grep ^SELINUX
SELINUXdisabled
SELINUXTYPEtargeted
[rootslave ~]# setenforce 0
[rootslave ~]# getenforce
Permissive3.下载软件包
[rootmaster ~]# ls
anaconda-ks.cfg mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz4.压缩软件包
[rootmaster ~]# tar xf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local
[rootmaster ~]# cd /usr/local
[rootmaster local]# ls
bin etc games include lib lib64 libexec mysql-8.0.35-linux-glibc2.28-x86_64 sbin share src
[rootmaster local]# mv mysql-8.0.35-linux-glibc2.28-x86_64 mysql
[rootmaster local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[rootmaster local]# 5.配置环境变量
[rootmaster local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[rootmaster local]# echo export PATH/usr/local/mysql/bin:$PATH /etc/profile.d/mysql.sh
[rootmaster local]# source /etc/profile.d/mysql.sh 6.做软连接
[rootmaster local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[rootmaster local]# ln -s /usr/local/mysql/include/ /usr/include/mysql7.告知lib在哪
[rootmaster local]# vim /etc/ld.so.conf.d/mysql.conf
[rootmaster local]# ldconfig -v8.添加帮助文档
[rootmaster local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[rootmaster local]# vim /etc/man_db.conf
#
MANDATORY_MANPATH /usr/man
MANDATORY_MANPATH /usr/share/man
MANDATORY_MANPATH /usr/local/share/man
MANDATORY_MANPATH /usr/local/mysql/man
#---------------------------------------------------------9.创建用户并修改/usr/local/mysql/的所有者和所属组为mysql
[rootmaster ~]# useradd -r -M -s /sbin/nologin mysql
[rootmaster ~]# id mysql
uid991(mysql) gid991(mysql) groups991(mysql)
[rootmaster ~]# chown -R mysql.mysql /usr/local/mysql
[rootmaster ~]# ll -d /usr/local/mysql
drwxr-xr-x. 9 mysql mysql 129 Dec 26 16:40 /usr/local/mysql10.创建数据库存放数据的目录并修改属组
[rootmaster ~]# mkdir /opt/data
[rootmaster ~]# chown -R mysql.mysql /opt/data
[rootmaster ~]# ll -d /opt/data
drwxr-xr-x. 2 mysql mysql 6 Dec 26 16:48 /opt/data11.初始化
[rootmaster ~]# mysqld --initialize --user mysql --datadir /opt/data
2023-12-26T08:49:43.219451Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 57766
2023-12-26T08:49:43.231426Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-26T08:49:43.579789Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-26T08:49:45.083767Z 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: otVyge;q7eY
[rootmaster ~]# echo otVyge;q7eY pass
[rootmaster ~]# cat pass
otVyge;q7eY12.向配置文件添加内容
[rootmaster ~]# vim /etc/my.cnf
[rootmaster ~]# cat /etc/my.cnf
[mysqld]
basedir /usr/local/mysql
datadir /opt/data
port 3306
socket /tmp/mysql.sock
pid-file /opt/data/mysql.pid
user mysql
skip-name-resolve13.给mysql服务指定位置
[rootmaster ~]# cd /usr/local/mysql
[rootmaster mysql]# ls
bin docs include lib LICENSE man README share support-files
[rootmaster mysql]# cd support-files/
[rootmaster support-files]# ls
mysqld_multi.server mysql-log-rotate mysql.server
[rootmaster support-files]# vim mysql.server
[rootmaster support-files]# cat mysql.server | grep ^datadir
datadir/opt/data
datadir_set
[rootmaster support-files]# cat mysql.server | grep ^basedir
basedir/usr/local/mysql
[rootmaster support-files]# systemctl daemon-reload14.启动服务
[rootmaster support-files]# service mysqld start
Starting MySQL.Logging to /opt/data/master.err.SUCCESS!
[rootmaster support-files]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:* 15.设置开机自启
[rootmaster ~]# cd /usr/lib/systemd/system
[rootmaster system]# cp sshd.service mysql.service
[rootmaster system]# vim mysql.service
[rootmaster system]# cat mysql.service
[Unit]
Descriptionmysql server daemon
Afternetwork.target sshd-keygen.target[Service]
Typeforking
ExecStartservice mysqld start
ExecStopservice mysqld stop
ExecReload/bin/kill -HUP $MAINPID[Install]
WantedBymulti-user.target
[rootmaster system]# service mysqld stop
Shutting down MySQL.ss. SUCCESS!
[rootmaster system]# systemctl daemon-reload
[rootmaster system]# systemctl status mysql
○ mysql.service - mysql server daemonLoaded: loaded (/usr/lib/systemd/system/mysql.service; disabled; preset: disabled)Active: inactive (dead)
[rootmaster system]# systemctl enable --now mysql
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /usr/lib/systemd/system/mysql.service.
[rootmaster system]# systemctl status mysql
● mysql.service - mysql server daemonLoaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: disabled)Active: active (running) since Tue 2023-12-26 17:06:55 CST; 7s agoProcess: 99845 ExecStartservice mysqld start (codeexited, status0/SUCCESS)Main PID: 99862 (mysqld_safe)Tasks: 39 (limit: 10820)Memory: 371.5MCPU: 829msCGroup: /system.slice/mysql.service├─ 99862 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir/opt/data --pid-file/opt/data/mysql.pid└─100052 /usr/local/mysql/bin/mysqld --basedir/usr/local/mysql --datadir/opt/data --plugin-dir/usr/local/mysql/lib/plugin --usermysql --log-errormaDec 26 17:06:53 master systemd[1]: Starting mysql server daemon...
Dec 26 17:06:55 master service[99849]: Starting MySQL. SUCCESS!
Dec 26 17:06:55 master systemd[1]: Started mysql server daemon.16.修改数据库密码
[rootmaster ~]# cat pass
otVyge;q7eY
[rootmaster ~]# mysql -uroot -potVyge;q7eY
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35Copyright (c) 2000, 2023, 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 alter user rootlocalhost identified with mysql_native_password by Passw0rd_~;
Query OK, 0 rows affected (0.00 sec)
mysql quit
Bye
[rootmaster ~]# mysql -uroot -pPassw0rd_~
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, 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 show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
4 rows in set (0.01 sec)
3.2 mysql主从配置
3.2.1 确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样先全备主数据库并还原到从数据库中
//先查看主库有哪些库
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
4 rows in set (0.00 sec)//再查看从库有哪些库
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
4 rows in set (0.00 sec)
3.2.2 在主数据库里创建一个同步账号授权给从数据库使用
[rootmaster ~]# mysql -uroot -pPassw0rd_~
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, 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 create user repl192.168.116.143 identified with mysql_native_password by Passw0rd_~;
Query OK, 0 rows affected (0.01 sec)mysql grant replication slave on *.* to repl192.168.116.143- ;
Query OK, 0 rows affected (0.00 sec)mysql flush privileges- ;
Query OK, 0 rows affected (0.01 sec)//到slave主机登入
[rootslave ~]# mysql -urepl -pPassw0rd_~ -h192.168.116.140
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, 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
3.2.3 配置主数据库
[rootmaster ~]# vim /etc/my.cnf
[rootmaster ~]# cat /etc/my.cnf
[mysqld]
basedir /usr/local/mysql
datadir /opt/data
port 3306
socket /tmp/mysql.sock
pid-file /opt/data/mysql.pid
user mysql
skip-name-resolvelog-bin mysql_bin
server-id 10
[rootmaster ~]# systemctl restart mysql
[rootmaster ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:* //查看主库的状态
mysql show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql_bin.000001 | 157 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)
3.2.4 配置从数据库
[rootslave ~]# vim /etc/my.cnf
[rootslave ~]# cat /etc/my.cnf
[mysqld]
basedir /usr/local/mysql
datadir /opt/data
port 3306
socket /tmp/mysql.sock
pid-file /opt/data/mysql.pid
user mysql
skip-name-resolverelay-log mysql_relay_bin
server-id 20
[rootslave ~]# systemctl restart mysql
[rootslave ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 70 *:33060 *:* //配置并启动主从复制
mysql change master to- master_host192.168.116.140,- master_userrepl,- master_passwordPassw0rd_~,- master_port3306,- master_log_filemysql_bin.000001,- master_log_pos157;
Query OK, 0 rows affected, 9 warnings (0.02 sec)mysql start slave- ;
Query OK, 0 rows affected, 1 warning (0.01 sec)//查看从服务器状态
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.116.140Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql_bin.000001Read_Master_Log_Pos: 157Relay_Log_File: mysql_relay_bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql_bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 157Relay_Log_Space: 536Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10Master_UUID: b6998aa8-a3cb-11ee-900f-000c29ed2b10Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)
3.2.5 测试验证
在主服务器创建库并创建表向表中插入数据
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
4 rows in set (0.01 sec)mysql create database student;
Query OK, 1 row affected (0.01 sec)mysql use student;
Database changed
mysql create table hl(id int not null,name varchar(20));
Query OK, 0 rows affected (0.02 sec)mysql desc hl;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.01 sec)
mysql insert hl(id,name) value(1,tom),(2,jerry);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from hl;
-----------
| id | name |
-----------
| 1 | tom |
| 2 | jerry |
-----------
2 rows in set (0.00 sec)
在从数据库中查看数据是否同步
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
--------------------
5 rows in set (0.01 sec)mysql select * from student.hl;
-----------
| id | name |
-----------
| 1 | tom |
| 2 | jerry |
-----------
2 rows in set (0.00 sec)
