MariaDB实现主从复制，mariadb实现主从
MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。 MariaDB跟MySQL在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。目前MariaDB是发展最快的MySQL分支版本，新版本发布速度已经超过了Oracle官方的MySQL版本。
1.环境准备：首先你要有两台相互ping的通的linux。大家可以使用VMware安装一台linux虚拟机，然后使用克隆方法，克隆出另外一台虚拟机。这里我使用的虚拟机是centos 7。需要注意的一点是克隆出另外一台虚拟机后需要在新克隆出的虚拟机的网络适配器的高级选项中重新生成一个MAC地址。
2.安装过程：有了两台虚拟机以后，我们就可以开始安装MariaDB了。首先我们要检查系统中是不是有自带的MariaDB，有的话卸载后再安装。
卸载过程：
停止服务：systemctl stop mariadb
查询安装包：rpm -qa |grep mariadb
如果查询到MariaDB的安装信息，就执行下面的命令，如果没有，直接跳过。
rpm -e mariadb-server
rpm -e mariadb
rpm -e –nodeps mariadb-libs
卸载完成后，执行命令：
yum -y install mariadb mariadb-server
安装完成后，拷贝/usr/share/mysql/my-huge.cnf到/etc目录下。即：cp /usr/share/mysql/my-huge.cnf /etc/my.cnf,如果已经存在，选择直接覆盖即可。然后编辑该文件：vim /etc/my.cnf，在[mysqld]后面添加一行：lower_case_table_names=1，设置MariaDB不区分表明大小写。然后启动MariaDB服务并设置开机自启动。
systemctl start mariadb
systemctl enable mariadb
做完这些步骤后，执行脚本：/usr/bin/mysql_secure_installation，按照提示操作数据库即可。
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 安装后默认没有root密码，直接回车
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] Y
New password: 输入root的新密码
Re-enter new password: 新密码确认
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] 删除匿名用户 Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] 关闭root远程登录 Y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] 删除test数据库 Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] 确定以上所有操作 Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!然后再另外一台linux虚拟机上重复以上操作，知道MariaDB安装成功。
接下来我们就可以配置MariaDB的主从了。
3.设置MariaDB主从
3.1选择一台linux虚拟机作为从节点，一台linux虚拟机作为主节点。然后到从节点上面做如下修改：vim /etc/my.cnf,修改server-id=2.
然后重启从节点：systemctl restart mariadb
3.2到主节点上创建一个slave
登陆MariaDB数据库：mysql -uroot -p密码
执行以下格式的命令：
GRANT REPLICATION SLAVE ON .{所有权限} TO ‘slave’@’%’{用户名为slave，%为任意地址} identified by ‘slave’;
即：GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘slave’;
在主节点查询master的状态:SHOW MASTER STATUS;
执行结果会像下面这样：
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1294 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)记住这里的File和Position信息。
然后在从节点上登陆MariaDB数据库，执行以下格式的命令：
CHANGE MASTER TO
MASTER_HOST=’主节点的IP地址’, MASTER_USER=’主节点授权的用户’, MASTER_PASSWORD=’主节点授权的用户的密码’,MASTER_LOG_FILE=’刚才几的File信息,MASTER_LOG_POS=刚才的Position信息;
即：
CHANGE MASTER TO MASTER_HOST='192.168.12.220',MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1294;然后重启主节点和从节点。
到从节点上执行命令：show slave status\G
不出意外的话将会看到下面的信息：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.220
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 245
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000004
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: 245
Relay_Log_Space: 1111
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: 1
1 row in set (0.00 sec)注意其中的
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两句。当你看到两个Yes时，表明你的MariaDB数据库的主从已经安装成功了。
如果有些同学因为一些意外没有成功，可以使用以下的方法解决：
首先停掉Slave服务：slave stop。然后到主服务器上查看主机的状态：
记录File和Position对应的值
进入master
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1294 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)然后到slave服务器上执行手动同步：
mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file= mysql-bin.000003',
> master_log_pos=1294;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)然后即可正常使用。
接下来我们分别远程连接两台MariaDB服务器，并且在主节点上进行数据的增删改，会发现从节点也跟着做了相应的操作。
如果你无法使用远程连接连接到MariaDB服务器,到MariaDB服务器上执行如下命令允许远程连接：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;授权root用户远程登录。