mariadb部署-主从

一、官网下载二进制安装包

下载地址:https://mariadb.com/downloads/

二、解压安装包

tar -xvf mariadb-10.8.5-linux-systemd-x86_64.tar.gz

三、创建mysql组和用户，指定GID和UID，便于集群管理

groupadd -g 3306 mysql
useradd -u 3306 -g 3306 -s /sbin/nologin mysql

四、拷贝mariadb程序到指定规划目录，并且创建mariadb程序路径和数据路径

mv mariadb-10.8.5-linux-systemd-x86_64 /usr/local/mysql
mkdir -p /data/mysql/db_file

五、初始化mysql

cd /usr/local/mysql/

#mariadb初始化如下：
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/db_file

六、修改my.cnf

[root@abcd1 ~]# cat /etc/my.cnf
[client]
port		    = 3306
socket		    = /tmp/mysql.sock

[mysqld]
port		    = 3306
socket		    = /tmp/mysql.sock
user            = mysql
basedir         = /usr/local/mysql
datadir         = /data/mysql/db_file
log_bin             = /data/mysql/bin-log
log_bin_index       = /data/mysql/mysql-bin.index
server_id           = 148
binlog_format       = ROW

init_connect='SET collation_connection=utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
skip-character-set-client-handshake
skip-name-resolve
slow_query_log_file = /data/mysql/log/slow-query.log
expire_logs_days  = 3
innodb_file_per_table = NO
max_connections = 1500
ft_min_word_len = 1
default-storage-engine = InnoDB
innodb_buffer_pool_size = 8G
innodb_log_file_size = 512M

[mysqld_safe]
open-files-limit = 8192
log-error=/data/mysql/log/mysqld.log
pid-file=/data/mysql/temp/mysqld.pid

七、创建mariadb所需的路径并授权

mkdir -p /data/mysql/{log,temp}
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /usr/local/mysql

八、修改启动文件

cd /usr/local/mysql
cp -p support-files/mysql.server /etc/init.d/mysqld

vim /etc/init.d/mysqld
#主要修改路径变量的值
basedir=/usr/local/mysql
datadir=/data/mysql/db_file
mysqld_pid_file_path=/data/mysql/temp/mysqld.pid

九、启动&自启动mariadb

service mysqld start  #请注意随机密码的生成保存，便于第一次登陆所需
chkconfig --add mysqld
chkconfig mysqld on

十、安全初始化mariadb

cd /usr/local/mysql/bin/mysql_secure_installation
...
Enter current password for root (enter for none):    #root无密码，直接回车
OK, successfully used password, moving on...
...
Set root password? [Y/n] y   #修改root密码
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y   #删除匿名用户
 ... Success!
...
Disallow root login remotely? [Y/n] n   #禁止root远程登陆
 ... Success!
...
Remove test database and access to it? [Y/n] n   #删除测试库
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y   #刷新权限
 ... Success!
...
Thanks for using MariaDB!

十一、声明环境变量

cat << EOF > /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
EOF

source /etc/profile

十二、创建用户并授权

mysql -uroot -p  #登录，密码在第九步启动时获取

create user root@'%';
set password for root@'%' =PASSWORD("password");
grant all privileges on *.* to root@'%';   
flush privileges;

#授权用户root所有地址允许远程连接
grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
flush privileges;  #刷新库

十三、主从配置

主节点my.cnf 添加：
log_bin             = /home/mysql/bin-log
log_bin_index       = /home/mysql/mysql-bin.index

server_id           = 160
binlog_format       = ROW 

从服务器my.cnf 添加
log_bin                   = /var/lib/mysql/bin-log
log_bin_index             = /var/lib/mysql/mysql-bin.index

binlog_format             = ROW

# 主从配置
server-id=162
```
#server-id一般为IP最后一位


重启两台数据库服务
service mysqld stop
service mysqld start

 十四、建立账户并授权开启主从配置

#在主服务器上建立帐户并授权slave
grant replication slave on *.* to root@172.29.63.222 identified by '对方password';
flush privileges;
# ps: 172.29.63.222 为从服务器的ip

#登录服务器查看master状态 并记录binlog 和position
MariaDB [(none)]> show master status
    -> ;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000001 |      644 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.000 sec)


#配置从节点
change master to master_host='172.29.63.150',master_user='root',master_password='对方password',master_log_file='bin-log.000001',master_log_pos=644;
# ps:172.29.63.150 为主节点ip
# bin-log.000001 为上面第三点的 File
# 644为上面第三点的 Position

然后start slave 最后看一下状态 是不是yes

互为主从就是互相都配置一下，以上配置从节点的配置需要在主节点上操作一次，主节点上start slave;之后才能正常查看状态。
show slave status\G

start slave;   #开启状态同步
stop slave;  #停止状态同步

保持一致，设置从节点为只读
show global variables like 'read%'; // 查看 read_only 状态
set global read_only=1; // 当前环境生效，重启后失效
# 可以到 /etc/my.cnf 添加一行 read_only=1，然后重启即可