一次mariadb-5.5.40主从环境搭建

<p class="MsoNormal"> 传统上:一个主可以有多从,但是一个从服务器只有一个主<span></span> </p><p class="MsoNormal"> 而<span>  </span>今:一从多主<span></span> </p><p class="MsoNormal">
</p><p class="MsoNormal"> PS:服务器版本需要保持一致:如果不一致,从服务器版本必须高于主服务器!当然,最好版本相同<span></span> </p><p class="MsoNormal"> 简单配置过程:<span></span> </p><p class="MsoListParagraph" style="margin-left:21.0pt;text-indent:-21.0pt;"> <span>一.</span><span>master</span> </p><p class="MsoNormal"> <span>1</span>,启用二进制日志<span></span> </p><p class="MsoListParagraph" style="margin-left:18.0pt;text-indent:-18.0pt;"> <span>1,  </span>设置一个在当前集群中唯一的<span>server-id</span> </p><p class="MsoListParagraph" style="margin-left:18.0pt;text-indent:-18.0pt;"> <span>2,  </span>创建一个有复制权限的账号(<span>REPLICATIONSLAVE ,REPLICATION CLIENT</span>)<span></span> </p><p class="MsoNormal"> <span>                                                                         </span> </p><p class="MsoNormal"> <span> </span> </p><p class="MsoNormal"> 二.<span>slave</span> </p><p class="MsoListParagraph" style="margin-left:18.0pt;text-indent:-18.0pt;"> <span>1,  </span>启用中继日志<span></span> </p><p class="MsoListParagraph" style="margin-left:18.0pt;text-indent:-18.0pt;"> <span>2,  </span>设置一个在当前集群中唯一的<span>server-id</span> </p><p class="MsoListParagraph" style="margin-left:18.0pt;text-indent:-18.0pt;"> <span>3,  </span>使用有复制权限用户账号连接至主服务器,并启动复制线程<span></span> </p><p>
</p><p class="MsoNormal"> 插播:备份</p><p class="MsoNormal">
</p><p class="MsoNormal"> <span>Mysqldunmp </span>实现备份是,由于本身对有些存储引荇的备份,如<span>myisa</span>只能从温备,<span>innodb</span>能够热备,备份需要启动一个大事务<span></span> </p><p class="MsoNormal"> <span>Lvm</span>快照,几乎热备,事先请求锁定数据库,创建快照,释放锁。工具<span>perl</span>脚本借助快照备份<span></span> </p><p class="MsoNormal"> <span>Xtrabckup</span>:单表导入导出,流式化传输备份,物理备份工具。真正使用需要考虑备份策略,如:全备加增量备份,全备加差异备份,<span>xtrabackup</span>本身支持增量,但是增量只会<span>innodb</span>备份,<span>myisa</span>是不支持的。默认<span>mysql</span>启用最好启用<span>innodb</span> </p><p class="MsoNormal">
</p><p class="MsoNormal"> rmp包:
mariadb-5.5.40-linux-x86_64.tar.gz

一,主从复制
mkdir -pv /mydata/data
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 mysql
chown -R mysql.mysql /mydata
tar xf mariadb-5.5.40-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mariadb-5.5.40-linux-x86_64 mysql
cd mysql/
chown -R root.mysql ./
scripts/mysql_install_db --user=mysql --datadir=/mydata/data
mkdir /etc/mysql
cp support-files/my-large.cnf /etc/mysql/my.cnf


vim  /etc/mysql/my.cnf
<span style="background-color:#E53333;">datadir=/mydata/data</span>
<span style="background-color:#E53333;">innodb_file_per_table = on</span>
<span style="background-color:#E53333;">skip_name_resolve = on</span>

cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

master配置:
vim  /etc/mysql/my.cnf
log-bin=master-bin  线上机器不能修改
server-id       = 1

master主机授权slave复制
/usr/local/mysql/bin/mysql
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON
. TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;



slave配置:</p><p class="MsoNormal"> <span>vim  /etc/mysql/my.cnf</span>
server-id       = 20
relay-log = relay-bin  中继日志
read-only = on  

master查看
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+read_only 是不能阻止所有人向里面写入数据的,只能组织普通账号
+
+组织所有人不能写的话,在配置文件中添加如下:
+read-only = on  
+service mysqld restart
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

在配置之前查看下master的日志
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      497 |              |                  |
+-------------------+----------+--------------+------------------+

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+因为是第一次复制,主从都是空的,指明从当前这一刻开始复制
+如果新加入的从服务器,而主服务器已经工作很久,这时候应备份主服务器的数据恢复到从服务器,在从备份的的位置向后复制
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

slave:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.131.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=497,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
Query OK, 0 rows affected (0.14 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+CHANGE MASTER TO 
+MASTER_HOST='192.168.131.139', MASTER主机ip
+MASTER_USER='repluser',     复制权限的用户
+MASTER_PASSWORD='replpass', 复制权限的用户的密码
+MASTER_LOG_FILE='master-bin.000001', 日志文件开始位置
+MASTER_LOG_POS=497,   日志文件数值(在master 数据库中使用SHOW MASTER STATUS;查看当前是多少便从多少开始!!如果新加入的从服务器,而主服务器已经工作很久,这时候应备份主服务器的数据恢复到从服务器,在从备份的的位置向后复制)
+MASTER_CONNECT_RETRY=5,     5秒钟复制一次
+MASTER_HEARTBEAT_PERIOD=2;  心跳信息时间间隔
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

完成后检测
MariaDB [(none)]> SHOW SLAVE STATUSG
1. row
               Slave_IO_State: 
                  Master_Host: 192.168.131.139
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 497
               Relay_Log_File: relay-bin.000001    已经开始
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
        <span style="background-color:#E53333;">     Slave_IO_Running: No       从服务器io线程</span>
<span style="background-color:#E53333;">            Slave_SQL_Running: No       从服务器sql线程</span>
              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: 497
              Relay_Log_Space: 245
              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: NULL
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: 0
1 row in set (0.00 sec)

启动slave io线程和sql线程
MariaDB [(none)]><span style="background-color:#E53333;"> START SLAVE; </span> 
Query OK, 0 rows affected (0.00 sec

而后查看slave的relay-bin.000001和relay-log.info已经存在
[root@mysql-slave ~]# ll /mydata/data/ 
-rw-rw---- 1 mysql mysql      245 Jul  4 23:26 relay-bin.000001
-rw-rw---- 1 mysql mysql       19 Jul  4 23:26 relay-bin.index
-rw-rw---- 1 mysql mysql       43 Jul  4 23:26 relay-log.info
drwx------ 2 mysql root      4096 Jul  4 22:09 test
[root@mysql-slave ~]# 


master端:
MariaDB [(none)]> SHOW MASTER STATUSG
1. row
            File: master-bin.000002
        Position: 677
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

MariaDB [(none)]> 


slave端:
MariaDB [(none)]> SHOW SLAVE STATUSG;
1. row *
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.131.139
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 776
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 809
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes



PS:在做mysql主从时,中间最好不要修改主机名。否则可能会出现Slave_IO_Running和Slave_SQL_Running起不来的情况!

MASTER:
MariaDB [(none)]> CREATE DATABASE markdb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use markdb
Database changed
MariaDB [markdb]> create table tn1 (id int);
Query OK, 0 rows affected (0.05 sec)


SLAVE:
MariaDB [(none)]> use markdb
Database changed
MariaDB [markdb]> show tables;
+------------------+
| Tables_in_markdb |
+------------------+
| tn1              |
+------------------+
1 row in set (0.06 sec)


帮助选项
MariaDB [markdb]> HELP CHANGE MASTER TO

option:
    MASTER_BIND = 'interface_name'   
  | MASTER_HOST = 'host_name'   主服务器地址
  | MASTER_USER = 'user_name'    有复制权限的用户名
  | MASTER_PASSWORD = 'password'  用户密码
  | MASTER_PORT = port_num      主服务器端口
  | MASTER_CONNECT_RETRY = interval   链接重试的时间间隔
  | MASTER_HEARTBEAT_PERIOD = interval 心跳检测的时间间隔
  | MASTER_LOG_FILE = 'master_log_name' 主服务器二进制日志文件
  | MASTER_LOG_POS = master_log_pos    二进制日志文件中的位置
  | RELAY_LOG_FILE = 'relay_log_name'   
  | RELAY_LOG_POS = relay_log_pos
      基于ssl复制使用的
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)   做环装复制时。


mysql环境变量:export PATH=/usr/local/mysql/bin:$PATH</p><div>
</div><p>
</p><p> </p>

0 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2015-07-06分类: Mariadb

标签: mariadb

发表评论

已有 2 条评论

加载中,请稍候...
  1. 咳咳咳
    咳咳咳
    July 29th, 2015 at 05:55 pm

    大神 我来也,你这么不更新文章了呢。

  2. 小黑啊
    小黑啊
    August 5th, 2015 at 09:44 am

    大拿 我来看你了.