一、准备

二、原理

三、mariadb部署

3.1、tar包离线编译部署方式

1、下载mariadb安装包

https://downloads.mariadb.com/MariaDB/mariadb-10.0.38/bintar-linux-x86_64/mariadb-10.0.38-linux-x86_64.tar.gz

2、上传到/opt目录

[root@master opt]# ll

total 331204

-rw-r--r--. 1 root root 339149067 May 13 10:17 mariadb-10.0.38-linux-x86_64.tar.gz

3、创建mariadb安装目录，并且将压缩包解压到改目录下

[root@master ~]# mkdir /usr/local/mariadb/

[root@master ~]# tar -zxvf /opt/mariadb-10.0.38-linux-x86_64.tar.gz -C /usr/local/mariadb/

4、进入到mariadb的安装目录，重命名

[root@master ~]# cd /usr/local/mariadb/

[root@master mariadb]# mv mariadb-10.0.38-linux-x86_64 mariadb-10.0.38

5、创建mysql用户

[root@master mariadb]# useradd -s /sbin/nologin -M mysql

6、mariadb指定路径和用户初始化

root@master mariadb]# cd mariadb-10.0.38/

[root@master mariadb-10.0.38]# ./scripts/mysql_install_db --basedir=/usr/local/mariadb/mariadb-10.0.38 --datadir=/usr/local/mariadb/mariadb-10.0.38/data/ --user=mysql

7、将原先的配置进行移动备份，将配置文件模板移动到/etc下

[root@master mariadb-10.0.38]# mv /etc/my.cnf /etc/my.cnf.bak

[root@master mariadb-10.0.38]# cp support-files/my-innodb-heavy-4G.cnf /etc/my.cnf

8、编辑配置文件

vim /etc/my.cnf

主节点：

[mysqld]

basedir=/usr/local/mariadb/mariadb-10.0.38

datadir=/usr/local/mariadb/mariadb-10.0.38/data

server-id = 1 #唯一标识

g-bin = master-bin #开启mysql binlog功能

binlog_format = ROW # binlog记录内容的方式，记录被操作的每一行

binlog-do-db = wordpress # 指定需要复制的数据库名，如果备份多个数据库，重复设置这个选项即可

备节点

mysqld]

basedir=/usr/local/mariadb/mariadb-10.0.38

datadir=/usr/local/mariadb/mariadb-10.0.38/data

server-id = 2 #唯一标识

g-bin = master-bin #开启mysql binlog功能

binlog_format = ROW # binlog记录内容的方式，记录被操作的每一行

binlog-do-db = wordpress # 指定需要复制的数据库名，如果备份多个数据库，重复设置这个选项即可

8、修改mariadb的所属用户

[root@master mariadb-10.0.38]# chown -R mysql:mysql /usr/local/mariadb/mariadb-10.0.38/

9、将启动脚本移动

[root@master mariadb-10.0.38]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

10、启动mariadb

[root@master mariadb-10.0.38]# /etc/init.d/mysqld start

11、创建软链接

[root@master ~]# ln -s /usr/local/mariadb/mariadb-10.0.38/bin/mysql /usr/bin

[root@master ~]# ln -s /usr/local/mariadb/mariadb-10.0.38/bin/mysqladmin /usr/bin

11、初始化数据库密码

[root@master ~]# mysqladmin -u root -h localhost password '123456'

3.2、rpm包离线部署

1、上传所有的RPM包到opt目录

2、使用命令本地安装mariadb

[root@master opt]# yum localinstall *.rpm -y

3、启动mariadb

[root@master opt]# systemctl start mariadb

[root@master opt]# systemctl enable mariadb

4、设置root密码

[root@master opt]# mysqladmin -u root password 123456

5、登录mariadb数据库

[root@master opt]# mysql -uroot -p123456

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 30

Server version: 10.3.15-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)]>

5、修改数据库配置

主数据库

[root@master ~]# vim /etc/my.cnf.d/server.cnf

[mysqld]

server-id = 1

log-bin=mysql-bin

从数据库

[root@node1 ~]# vim /etc/my.cnf.d/server.cnf

[mysqld]

server-id = 2

四、数据库同步操作

主数据库

1、 登录数据库

[root@master ~]# mysql -uroot -p123456

2、 创建slave用户，并设置密码，然后赋予权限

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by '123456';

3、刷新

MariaDB [(none)]> flush privileges;

4、查看master状态

MariaDB [(none)]> show master status;

+------------------+----------+-----------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+-----------------+------------------+

| mysql-bin.000008 | 439 | wordpress,zrlog | |

+------------------+----------+-----------------+------------------+

1 row in set (0.00 sec)

从数据库

1、 建立连接

MariaDB [(none)]>

CHANGE MASTER TO MASTER_HOST='10.100.1.10', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=638; （必须和master一样）

2、 启动连接

MariaDB [(none)]> start slave;

3、 验证查看（有两个连接为yes，则表示成功）

MariaDB [(none)]> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.100.1.10

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 439

Relay_Log_File: node1-relay-bin.000006

Relay_Log_Pos: 726

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

五、测试

1、在主节点上创建一个ceshi的数据库

MariaDB [(none)]> create database woredpress123;

Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| ceshi |

| information_schema |

| mysql |

| performance_schema |

| test |

| wordpress |

| wordpress123 |

| xiaoyang |

+--------------------+

2、在从节点查看是否同步过来

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| ceshi |

| information_schema |

| mysql |

| performance_schema |

| test |

| wordpress |

| wordpress123 |

| xiaoyang |

+--------------------+

陕西耀州

湖北神狐

六、数据库简单操作

1、创建数据库

MariaDB [(none)]> create database xiaoyang;

2、创建表

MariaDB [(none)]>use xiaoyang;

MariaDB [xiaoyang]> create table test(id int,name varchar(10),mail varchar(30));

MariaDB [xiaoyang]> desc test;

3、向表内插入数据

MariaDB [xiaoyang]> insert into test(id,name,mail) values(1,'uos1','uos1@uos.com');

4、 查看数据

MariaDB [xiaoyang]> select * from test;

+------+------+--------------+

| id | name | mail |

+------+------+--------------+

| 1 | uos1 | uos1@uos.com |

5、 查看从数据库是否有数据

MariaDB [xiaoyang]> select * from test;

+------+------+--------------+

| id | name | mail |

+------+------+--------------+

| 1 | uos1 | uos1@uos.com |

+------+------+--------------+