mysql主从
2021/10/11 19:16:01
本文主要是介绍mysql主从，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
mysql主从
主从安装mariadb [root@master ~]# yum -y install mariadb* [root@slave ~]# yum -y install mariadb* zabbix服务启动 [root@zabbix ~]# zabbix_server [root@zabbix ~]# zabbix_agentd [root@zabbix ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 32 192.168.122.1:53 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 5 127.0.0.1:631 0.0.0.0:* LISTEN 0 128 0.0.0.0:10050 0.0.0.0:* LISTEN 0 128 0.0.0.0:10051 0.0.0.0:* LISTEN 0 128 127.0.0.1:9000 0.0.0.0:* LISTEN 0 128 0.0.0.0:111 0.0.0.0:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 5 [::1]:631 [::]:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:111 [::]:* LISTEN 0 128 *:80 *:* 主从开启mysql [root@master ~]# systemctl start mariadb [root@slave ~]# systemctl start mariadb 主从关闭防火墙 [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 ~]# setenforce 0 //从服务器已经关闭过
主服务器操作
主创建权限账户 [root@master ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.145.171' identified by 'repl123!'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> quit Bye 进入此目录找到mysqld添加后两行 [root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid server-id = 10 log-bin = mysql_bin 进入/var/lib/mysql/下查看是否添加mysql_bin 如果没有重启 [root@master ~]# ll /var/lib/mysql/ 总用量 122936 -rw-rw----. 1 mysql mysql 16384 10月 11 02:45 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 10月 11 02:45 aria_log_control -rw-rw----. 1 mysql mysql 972 10月 11 02:45 ib_buffer_pool -rw-rw----. 1 mysql mysql 12582912 10月 11 02:45 ibdata1 -rw-rw----. 1 mysql mysql 50331648 10月 11 02:45 ib_logfile0 -rw-rw----. 1 mysql mysql 50331648 10月 11 02:45 ib_logfile1 -rw-rw----. 1 mysql mysql 12582912 10月 11 02:45 ibtmp1 -rw-rw----. 1 mysql mysql 0 10月 11 02:45 multi-master.info drwx------. 2 mysql mysql 4096 10月 11 02:45 mysql srwxrwxrwx. 1 mysql mysql 0 10月 11 02:45 mysql.sock -rw-rw----. 1 mysql mysql 16 10月 11 02:45 mysql_upgrade_info drwx------. 2 mysql mysql 20 10月 11 02:45 performance_schema -rw-rw----. 1 mysql mysql 24576 10月 11 02:45 tc.log [root@master ~]# systemctl restart mariadb [root@master ~]# ll /var/lib/mysql/ 总用量 122920 -rw-rw----. 1 mysql mysql 16384 10月 11 02:55 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 10月 11 02:55 aria_log_control -rw-rw----. 1 mysql mysql 976 10月 11 02:55 ib_buffer_pool -rw-rw----. 1 mysql mysql 12582912 10月 11 02:55 ibdata1 -rw-rw----. 1 mysql mysql 50331648 10月 11 02:55 ib_logfile0 -rw-rw----. 1 mysql mysql 50331648 10月 11 02:45 ib_logfile1 -rw-rw----. 1 mysql mysql 12582912 10月 11 02:55 ibtmp1 -rw-rw----. 1 mysql mysql 0 10月 11 02:45 multi-master.info drwx------. 2 mysql mysql 4096 10月 11 02:45 mysql -rw-rw----. 1 mysql mysql 328 10月 11 02:55 mysql_bin.000001 -rw-rw----. 1 mysql mysql 19 10月 11 02:55 mysql_bin.index srwxrwxrwx. 1 mysql mysql 0 10月 11 02:55 mysql.sock -rw-rw----. 1 mysql mysql 16 10月 11 02:45 mysql_upgrade_info drwx------. 2 mysql mysql 20 10月 11 02:45 performance_schema MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql_bin.000001 | 328 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.000 sec)
从库操作
修i该配置文件
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id = 20
relay-log = myrelay
MariaDB [(none)]> change master to
-> master_host='192.168.145.169',
-> master_user='repl',
-> master_password='repl123!',
-> master_log_file='mysql_bin.000002',
-> master_log_pos=328;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.145.169
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 328
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql_bin.000002
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: 328
Relay_Log_Space: 856
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_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
授权zabbix账户 MariaDB [(none)]> grant select on *.* to 'zabbix'@'localhost' identified by 'zabbix123!'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> grant super,replication client on *.* to 'zabbix'@'localhost' identified by 'zabbix123!'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) 如果关闭slave执行脚本会显示1 MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.001 sec) [root@slave scripts]# ./check_replication.sh 1 [root@slave ~]# vim /usr/local/etc/zabb
声音报警
mysql主从延迟
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.145.169
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 328
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql_bin.000002
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: 328
Relay_Log_Space: 856
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_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
[root@slave scripts]# chmod +x check_replication_delay.sh
[root@slave scripts]# cat check_replication.sh
#!/bin/bash
delay=$(mysql -uzabbix -p'zabbix123!' -e 'show slave status\G'|grep 'Behind'|awk '{print $2}')
if [ $delay != NULL ];then
echo $delay
else
echo '0'
fi
[root@slave scripts]# ./check_replication_delay.sh
0
//修改zabbix_agentd.conf配置文件
[root@slave ~]# vim /usr/local/etc/zabbix_agentd.conf
525 UserParameter=check_replication,/scripts/check_replication.sh
526 UserParameter=check_replication_delay,/scripts/check_replicatio n_delay.sh
[root@slave ~]# pkill zabbix
[root@slave ~]# zabbix_agentd
[root@zabbix ~]# zabbix_get -s 192.168.145.171 -k check_replication_delay
0
用户和组权限的设置
[root@zabbix ~]# mysql -uroot -pzs123!
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 2461
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
5 rows in set (0.01 sec)
mysql> use zabbix;
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> show tables;
+----------------------------+
| Tables_in_zabbix |
+----------------------------+
| acknowledges |
| actions |
| alerts |
| auditlog |
| auditlog_details |
| autoreg_host |
| conditions |
| config |
| config_autoreg_tls |
| corr_condition |
| corr_condition_group |
| corr_condition_tag |
| corr_condition_tagpair |
| corr_condition_tagvalue |
| corr_operation |
| correlation |
| dashboard |
| dashboard_page |
| dashboard_user |
| dashboard_usrgrp |
| dbversion |
| dchecks |
| dhosts |
| drules |
| dservices |
| escalations |
| event_recovery |
| event_suppress |
| event_tag |
| events |
| expressions |
| functions |
| globalmacro |
| globalvars |
| graph_discovery |
| graph_theme |
| graphs |
| graphs_items |
| group_discovery |
| group_prototype |
| history |
| history_log |
| history_str |
| history_text |
| history_uint |
| host_discovery |
| host_inventory |
| host_tag |
| hostmacro |
| hosts |
| hosts_groups |
| hosts_templates |
| housekeeper |
| hstgrp |
| httpstep |
| httpstep_field |
| httpstepitem |
| httptest |
| httptest_field |
| httptest_tag |
| httptestitem |
| icon_map |
| icon_mapping |
| ids |
| images |
| interface |
| interface_discovery |
| interface_snmp |
| item_condition |
| item_discovery |
| item_parameter |
| item_preproc |
| item_rtdata |
| item_tag |
| items |
| lld_macro_path |
| lld_override |
| lld_override_condition |
| lld_override_opdiscover |
| lld_override_operation |
| lld_override_ophistory |
| lld_override_opinventory |
| lld_override_opperiod |
| lld_override_opseverity |
| lld_override_opstatus |
| lld_override_optag |
| lld_override_optemplate |
| lld_override_optrends |
| maintenance_tag |
| maintenances |
| maintenances_groups |
| maintenances_hosts |
| maintenances_windows |
| media |
| media_type |
| media_type_message |
| media_type_param |
| module |
| opcommand |
| opcommand_grp |
| opcommand_hst |
| opconditions |
| operations |
| opgroup |
| opinventory |
| opmessage |
| opmessage_grp |
| opmessage_usr |
| optemplate |
| problem |
| problem_tag |
| profiles |
| proxy_autoreg_host |
| proxy_dhistory |
| proxy_history |
| regexps |
| report |
| report_param |
| report_user |
| report_usrgrp |
| rights |
| role |
| role_rule |
| script_param |
| scripts |
| service_alarms |
| services |
| services_links |
| services_times |
| sessions |
| sysmap_element_trigger |
| sysmap_element_url |
| sysmap_shape |
| sysmap_url |
| sysmap_user |
| sysmap_usrgrp |
| sysmaps |
| sysmaps_element_tag |
| sysmaps_elements |
| sysmaps_link_triggers |
| sysmaps_links |
| tag_filter |
| task |
| task_acknowledge |
| task_check_now |
| task_close_problem |
| task_data |
| task_remote_command |
| task_remote_command_result |
| task_result |
| timeperiods |
| token |
| trends |
| trends_uint |
| trigger_depends |
| trigger_discovery |
| trigger_queue |
| trigger_tag |
| triggers |
| users |
| users_groups |
| usrgrp |
| valuemap |
| valuemap_mapping |
| widget |
| widget_field |
+----------------------------+
166 rows in set (0.00 sec)
mysql> select * from users\G
*************************** 1. row ***************************
userid: 1
username: Admin
name: Zabbix
surname: Administrator
passwd: $2y$10$92nDno4n0Zm7Ej7Jfsz8WukBfgSS/U0QkIuu8WkJPihXBb2A1UrEK
url:
autologin: 1
autologout: 0
lang: default
refresh: 30s
theme: default
attempt_failed: 0
attempt_ip:
attempt_clock: 0
rows_per_page: 50
timezone: default
roleid: 3
*************************** 2. row ***************************
userid: 2
username: guest
name:
surname:
passwd: $2y$10$89otZrRNmde97rIyzclecuk6LwKAsHN0BcvoOKGjbT.BwMBfm7G06
url:
autologin: 0
autologout: 15m
lang: default
refresh: 30s
theme: default
attempt_failed: 0
attempt_ip:
attempt_clock: 0
rows_per_page: 50
timezone: default
roleid: 4
2 rows in set (0.00 sec)
mysql>
这篇关于mysql主从的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-12-25如何部署MySQL集群资料：新手入门教程
- 2024-12-24MySQL集群部署资料：新手入门教程
- 2024-12-24MySQL集群资料详解：新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习：新手入门教程
- 2024-12-24部署MySQL集群入门：一步一步搭建指南
- 2024-12-07MySQL读写分离入门：轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
