用 KVM 搭建web集群实验笔记 - MySQL多实例和主从复制

MySQL多实例和主从复制

1. 编译方式安装 M ySQL

groupadd mysql
useradd -g mysql -M -s /sbin/nologin mysql
yum -y install make gcc-c++ cmake  ncurses-devel libaio-devel
cd /_software/mysql-5.5.51

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306

make
make install

2. 配置MySQL多实例

建目录

mkdir -p /data/3306/data
mkdir -p /data/3307/data  

编辑/data/3306/my.cnf

 #/data/3306/my.cnf 
    [client] 
    port            = 3306 
    socket          = /data/3306/mysql.sock 
    [mysql] 
    no-auto-rehash 
     
    [mysqld] 
    user    = mysql 
    port    = 3306
    socket  = /data/3306/mysql.sock 
    basedir = /usr/local/mysql 
    datadir = /data/3306/data 
    open_files_limit    = 1024 
    back_log = 600 
    max_connections = 800 
    max_connect_errors = 3000 
    table_cache = 614 
    external-locking = FALSE 
    max_allowed_packet =8M 
    sort_buffer_size = 1M 
    join_buffer_size = 1M 
    thread_cache_size = 100 
    thread_concurrency = 2 
    query_cache_size = 2M 
    query_cache_limit = 1M 
    query_cache_min_res_unit = 2k 
    #default_table_type = InnoDB 
    thread_stack = 192K 
    #transaction_isolation = READ-COMMITTED 
    tmp_table_size = 2M 
    max_heap_table_size = 2M 
    long_query_time = 1 
    pid-file = /data/3306/mysql.pid
    relay-log = /data/3306/relay-bin
    relay-log-info-file = /data/3306/relay-log.info
    binlog_cache_size = 1M 
    max_binlog_cache_size = 1M 
    max_binlog_size = 2M 
    expire_logs_days = 7 
    key_buffer_size = 16M 
    read_buffer_size = 1M 
    read_rnd_buffer_size = 1M 
    bulk_insert_buffer_size = 1M 
    lower_case_table_names = 1 
    skip-name-resolve
    slave-skip-errors = 1032,1062
    replicate-ignore-db = mysql

    server-id = 1 
     
    innodb_additional_mem_pool_size = 4M 
    innodb_buffer_pool_size = 32M 
    innodb_data_file_path = ibdata1:128M:autoextend 
    innodb_file_io_threads = 4 
    innodb_thread_concurrency = 8 
    innodb_flush_log_at_trx_commit = 2 
    innodb_log_buffer_size = 2M 
    innodb_log_file_size = 4M 
    innodb_log_files_in_group = 3 
    innodb_max_dirty_pages_pct = 90 
    innodb_lock_wait_timeout = 120 
    innodb_file_per_table = 0
 
    [mysqldump] 
    quick 
    max_allowed_packet = 2M 
     
    [mysqld_safe] 
    log-error=/data/3306/mysql_err.log 
    pid-file=/data/3306/mysqld.pid 

编辑/data/3307/my.cnf
对照/data/3306/my.cnf 将其中3306改成3307，将server-id = 1 改成 server-id = 3
编辑/data/3306/mysqld   并用chmod +x 变成可执行

#!/bin/bash
PORT=3306
MYSQL_USER="root"
MYSQL_PWD="123456"
MYSQL_PATH="/usr/local/mysql/bin"
MYSQL_SOCK="/data/${PORT}/mysql.sock"

#start MySQL instance
start_mysql(){
    if [ ! -e "$MYSQL_SOCK" ]; then
        printf "Starting MySQL...\n"
        /bin/sh ${MYSQL_PATH}/mysqld_safe --defaults-file=/data/${PORT}/my.cnf 2>&1 > /dev/null &
    else
        printf "MySQL is running...\n"
        exit
    fi
}

#stop MySQL instance
stop_mysql(){
    if [ ! -e "$MYSQL_SOCK" ]; then
        printf "MySQL is stopped...\n"
        exit
    else
        printf "Stoping MySQL...\n"
        ${MYSQL_PATH}/mysqladmin -u ${MYSQL_USER} -p${MYSQL_PWD} -S /data/${PORT}/mysql.sock shutdown
    fi
}

#restart MySQL instance
restart_mysql(){
    printf "Restarting MySQL...\n"
    stop_mysql
    sleep 2
    start_mysql
}

case $1 in
start)
     start_mysql
;;
stop)
    stop_mysql
;;
restart)
    restart_mysql
;;
*)
    printf "Usage: /data/${PORT}/mysqld {start|stop|restart}\n"
esac

编辑/data/3307/mysqld
对照/data/3306/mysqld 将其中PORT=3306改成PORT=3307

初始化数据库

cd /usr/local/mysql/scripts/
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --usr=mysql
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --usr=mysql

启动数据库

/data/3306/mysqld start
/data/3307/mysqld start

用mysqladmin -u root -S /data/3306/mysql.sock password '123456' 设置密码

3.配置主从复制

3306为主库，3307为从库

主库开启binlog

编辑/data/3306/my.cnf

在[mysqld]节增加log-bin=/data/3306/mysql-bin 

主库的配置

[mysqld]

    server-id = 1
    log-bin=/data/3306/mysql-bin

重启主数据库

/data/3306/mysqld restart

验证binlog是否启动

/usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3306/mysql.sock

mysql> 
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

主库设置主从复制的帐号

mysql> grant replication slave on *.* to 'rep'@'192.168.122.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| root | 127.0.0.1     |
| rep  | 192.168.122.% |
| root | ::1           |
|      | localhost     |
| root | localhost     |
|      | mysql         |
| root | mysql         |
+------+---------------+
7 rows in set (0.00 sec)

mysql> show grants for rep@'192.168.122.%';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.122.%                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.122.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

迁移主库数据到从库

1)锁表

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 120      |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      260 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2）另开一个终端，打包数据文件

[root@mysql ~]# mkdir -p /_backup
[root@mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'123456' -S /data/3306/mysql.sock --events -A -B |gzip > /_backup/mysql_bak.$(date +%F).sql.gz
[root@mysql ~]# ls /_backup
mysql_bak.2016-09-15.sql.gz
[root@mysql ~]# 

3）解除锁定

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> 

从库上的操作

1）检查从库的server_id，必须与主库不同，关闭binlog

[root@mysql ~]# egrep "server-id|log-bin" /data/3307/my.cnf
    server-id = 3
[root@mysql ~]# 

/usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3307/mysql.sock

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'server_id' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

2）把主库导出的数据恢复到从库

[root@mysql ~]# cd /_backup
[root@mysql _backup]# ls
mysql_bak.2016-09-15.sql.gz
[root@mysql _backup]# gzip -d mysql_bak.2016-09-15.sql.gz
[root@mysql _backup]# ls
mysql_bak.2016-09-15.sql
[root@mysql _backup]#  
[root@mysql _backup]# /usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3307/mysql.sock <mysql_bak.2016-09-15.sql
[root@mysql _backup]# 

配置复制参数，执行以下命令，其中MASTER_LOG_FILE和MASTER_LOG_POS用 show master status命令看到的数据

/usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3307/mysql.sock << EOF
CHANGE MASTER TO
    MASTER_HOST='192.168.122.31',
    MASTER_PORT=3306,
    MASTER_USER='rep',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=260;
EOF

启动从库同步

[root@mysql _backup]# /usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3307/mysql.sock -e "start slave";
[root@mysql _backup]# /usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3307/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.31
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 260
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           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: 260
              Relay_Log_Space: 403
              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
[root@mysql _backup]# 

测试

[root@mysql 3306]# /usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3306/mysql.sock -e  "create database mytest;"
[root@mysql 3306]# /usr/local/mysql/bin/mysql -uroot -p'123456' -S /data/3307/mysql.sock -e  "show databases like 'mytest';"
+-------------------+
| Database (mytest) |
+-------------------+
| mytest            |
+-------------------+
[root@mysql 3306]#