Debian配置双机热备（Mariadb）Master端及发布phpMyadmin-lnxserver1

赛题

2.1.6、数据库的双机热备（MariaDB）Master端

· 在lnxserver1上完成MariaDB数据库的安装，添加数据库root用户密码为Skills46;

· 安装MariaDB 数据库服务器组件;

· MariaDB数据库管理员信息：User: root/ Password: Skills46，

· 安装MariaDB WEB 管理面板 “phpMyAdmin”，通过apache 进行发布；

· 安装phpMyAdmin ，MariaDB 的web管理面板组件；

· 安装apache，配置php环境，用于发布phpMyAdmin，

· 配置lnxserver1数据库复制功能；（master）数据库服务器布；

· 在lnxserver1 配置MariaDB主复制端配置信息，允许远程复制

· 添加用于双机热备，从复制端的接入信息及用于复制的用户名及密码；User:datauser;Password:Skills46

· lnxserver1上创建的数据库“WordPress”的备份；

· 设置数据库的备份；备份数据库到 /var/www/backup;目录下需要有一个手动备份的数据库文件，格式为备份的日期+时间+版本（20200415103001.sql），备份密码为：Skills46；（本题安装配置过程中涉及密码皆为“Skills46”）；

1.安装服务

root@lnxserver1:~# apt install -y apache2 php* mariadb-server lrzsz unzip
root@lnxserver1:~# rz
rz waiting to receive.
Starting zmodem transfer.  Press Ctrl+C to cancel.
Transferring phpMyAdmin-4.4.15.10-all-languages.zip...
phpMyAdmin-4.4.15.10-all-languages.zip was skipped

root@lnxserver1:~# unzip phpMyAdmin-4.4.15.10-all-languages.zip
root@lnxserver1:~# mv phpMyAdmin-4.4.15.10-all-languages /var/www/html/phpMyAdmin

2.配置phpMyadmin

root@lnxserver1:~# cd /var/www/html/phpMyAdmin
root@lnxserver1:# cp -a config.sample.inc.php  config.inc.php
root@lnxserver1:# vim config.inc.php 
 31 $cfg['Servers'][$i]['host'] = '172.0.10.120';
 34 $cfg['Servers'][$i]['AllowNoPassword'] = true;

3.配置数据库

root@lnxserver1:~# mysql -uroot -p
MariaDB [(none)]> create database phpmyadmin;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'Skills46' WITH GRANT OPTION;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> Ctrl-C -- exit!
Aborted
root@lnxserver1:/var/www/html# chmod 755 -R phpMyAdmin/  

4.测试

5.主从复制，双机热备

主复制端

root@lnxserver1:~# vim /etc/mysql/my.cnf
[mysqld]
skip-name-resolve
log-bin=mysql-bin
log-slave-updates=true
server-id=20

root@lnxserver1:~# systemctl restart mariadb 
root@lnxserver1:~# mysql
MariaDB [(none)]> create user datauser@'%' identified by 'Skills46';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant replication slave on *.* to datauser@'%' identified by 'Skills46';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> 
root@lnxserver1:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf 
 28 bind-address            = *

从复制端

root@lnxserver2:~# vim /etc/mysql/my.cnf
[mysqld]
skip-name-resolve
log-bin=mysql-bin
log-slave-updates=true
server-id=10	#在[mysqld]模块中添加主机ID，该ID不能和和主数据库的一致
root@lnxserver1:~# systemctl restart mariadb 
root@lnxserver1:~# mysql
MariaDB [(none)]> change master to
    -> master_host='172.0.10.120',
    -> master_user='datauser',
    -> master_password='Skills46',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=328;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;                        
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.0.10.120
                   Master_User: datauser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 328
                Relay_Log_File: mysqld-relay-bin.000003
                 Relay_Log_Pos: 627
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes		#这两个必须YES
             Slave_SQL_Running: Yes		#这两个必须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: 1308
               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: 20
                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)

ERROR: No query specified

MariaDB [(none)]> 

主从复制测试

root@lnxserver1:~# mysql
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.001 sec)

root@lnxserver2:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10

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)]> show databases;     
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> 

6.数据库备份

root@lnxserver1:~# mkdir /var/www/backup
root@lnxserver1:~# vim /mysqlbk.sh 
mysqldump -uroot -pSkills46 wordpress > /var/www/backup/"`date +%Y%m%d%H%M%S`".sql
root@lnxserver1:~# ls -l /var/www/backup/
-rw-r--r-- 1 root root 46155 Feb 16 14:04 20230216140431.sql