MariaDB集群搭建

1、主节点配置：
yum install mariadb-server.x86_64 -y#安装数据库
service mariadb start#启动数据库
mysql -uroot -p123456#登录数据库
创建从节点连接登录用户
create user 'repl'@'192.168.2.%' identified by 'repl';
给用户授权
grant replication slave on *.* to 'repl'@'192.168.2.%' identified by 'repl';
vim /etc/my.cnf
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin=/var/lib/mysql/mysql-bin
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid


#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启数据库
service mariadb restart
进入数据库
mysql -uroot -p123456
查看服务器id是否生效
MariaDB [(none)]> show variables like 'server_id';

2、从节点配置：
yum install mariadb-server.x86_64 -y#安装数据库
service mariadb start#启动数据库
mysql -uroot -p123456#登录数据库
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2
relay-log=/var/lib/mysql/relay-bin
relay-log-index=/var/lib/mysql/relay-bin.index
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启数据库
service mariadb restart
进入数据库
mysql -uroot -p123456
查看服务器id是否生效
MariaDB [(none)]> show variables like 'server_id';
重置从节点设置
MariaDB [(none)]> reset slave;
配置从节点连接信息
MariaDB [(none)]> change master to master_host='192.168.2.121',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=0;
启动从节点连接
MariaDB [(none)]> start slave;
查看从节点运行状态
MariaDB [(none)]> show slave status\G


3、PS
备份指定数据库：
mysqldump -uroot -p'12345678' mytest > /var/lib/mysql/mysqlbak/mysql_bak_$(date "+%Y-%m-%d_%H:%M:%S").sql
恢复指定数据库：
 mysql -D mytest -uroot -p'12345678' < /var/lib/mysql/mysqlbak/mysql_bak_2019-07-08_19\:59\:34.sql
 
mysql -V#查看版本
rpm -ql mariadb-server#查看安装文件
ss -ntl#查看端口运行状态，3306端口
ss -utlnp
service mariadb start#启动mysql
mysql -u [用户名] -p　-h [远程主机地址]#连接到数据
mysql　-urepl -prepl -h192.168.2.121#远程登录数据库
show　databases；#查看数据库
use　mysql#进入数据库
mysql_secure_installation#执行安全加固脚本
mysql -uroot -p12345678 < test.sql#执行脚本
MariaDB [(none)]> source /root/test.sql#执行脚本
show databases;#显示数据库
create table user(id int,name varchar(20),age int);#创建表
insert into user(id,name,age) values(1,trent,20);#向表中插入数据
create table test(id int) engine=myisam;#创建表指定引擎
show create table user;#查看数据表用的引擎
cat /etc/my.cnf#查看数据库配置

转载于:https://www.cnblogs.com/fourw/p/11153256.html