Category Archives: 数据库

mysql

Leave a reply

查询当前的部署模式

部署主从

主服务器配置

修改配置文件 /etc/my.cnf

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

重启mysql，登录mysql控制台，创建用于同步的用户账号：

mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限
mysql> flush privileges;   #刷新权限

查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

从服务器配置

同样找到my.cnf配置文件，添加server-id

[mysqld]
server-id=2 #设置server-id，必须唯一

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='182.92.172.80',
    ->     MASTER_USER='rep1',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=73;

启动slave同步进程

mysql>start slave;

查看slave状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.92.172.80
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 11662
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

还可以用到的其他相关参数：

binlog-do-db：指定mysql的binlog日志记录哪个db
Replicate_Do_DB：参数是在slave上配置，指定slave要复制哪个库

在master上设置binlog_do_弊端：
1、过滤操作带来的负载都在master上
2、无法做基于时间点的复制（利用binlog）。

需要注意的是最好在从服务器的my.cnf里设置read_only选项，防止发生意外（连接用户不能有SUPER权限，否则无效）。

记得先手动同步一下主从服务器：

  • 数据量小：可以用mysqldump，它有一个master-data参数很有用，通过使用此参数，导出的SQL文件里会自动包含CHANGE MASTER TO MASTER_LOG_FILE=’…’, MASTER_LOG_POS=…;，这样创建从服务器就更方便了。
  • 数据量大：不太适合使用mysqldump（慢），如果是myisam表的话，加上–lock-all-tables参数，如果是innodb表的话，加上–single-transaction参数。而应该采用拷贝文件的方式，请按如下操作步骤：

先在主服务器上锁定所有的表，以免在复制过程中数据发生变化：mysql> flush tables with read lock;

然后在主服务器上查询当前二进制文件的文件名及偏移位置：mysql > show master status;

然后停止主服务器上的MySQL服务：shell> mysqladmin -u root shutdown

注意：如果仅是MyISAM的话，可以不停止MySQL服务，但要在复制数据文件的过程中保持只读锁，如果是InnoDB的话，必须停止MySQL服务。

再拷贝数据文件：shell> tar -cvf /tmp/mysql-snapshot.tar .

拷贝完别忘了启动主服务上的MySQL服务了。

然后把数据文件应用到从服务器上，再次启动slave的时候使用，记得启动时加上skip-slave-start选项，使之不会立刻去连接master，再在从服务器上设置相关的二进制日志信息：

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;

启动从服务器上的复制线程：mysql> start slave;

验证主从设置是否已经成功：mysql> show slave status\G;