Linux运维 --- 配置MySQL主从服务器

随着访问量的不断增加，Mysql数据库压力不断增加，需要对mysql进行优化和架构改造，可以使用高可用、主从复制、读写分离来、拆分库、拆分表进行优化。

hostname	IP地址
Master	192.168.203.224
Slave	192.168.203.223

MySQL主从配置

安装MySQL

[root@master ~]# yum -y install gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel ncurses-devel sudo bzip2 flex libaio libaio-devel
[root@master ~]# wget http://www.cmake.org/files/v2.8/cmake-2.8.11.2.tar.gz
[root@master ~]# tar -xf cmake-2.8.11.2.tar.gz 
[root@master ~]# cd cmake-2.8.11.2/
[root@master ~]# ./configure
[root@master ~]# make && make install
[root@master ~]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
[root@master ~]# 
[root@master ~]# wget http://downloads.mysql.com/archives/mysql-5.6/mysql-5.6.19.tar.gz
[root@master ~]# tar -xf mysql-5.6.19.tar.gz -C /usr/local/
[root@master ~]# cd /usr/local/mysql-5.6.19/
[root@master mysql-5.6.19]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/var -DSYSCONFDIR=/etc
[root@master mysql-5.6.19]# make && make install
[root@master mysql-5.6.19]# mkdir -p /usr/local/mysql/var
[root@master mysql-5.6.19]# chown -Rf mysql:mysql /usr/local/mysql
[root@master mysql-5.6.19]# rm -rf /etc/my.cnf
[root@master mysql-5.6.19]# cd /usr/local/mysql
[root@master mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var
[root@master mysql]# ln -s my.cnf /etc/my.cnf
[root@master mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@master mysql]# chmod +x /etc/rc.d/init.d/mysqld
[root@master mysql]# vim /etc/rc.d/init.d/mysqld
  46 basedir=/usr/local/mysql
  47 datadir=/usr/local/mysql/var
[root@master mysql]# /etc/rc.d/init.d/mysqld start
Starting MySQL..... SUCCESS! 
[root@master mysql]# vim /etc/profile
  export PATH=/usr/local/mysql/bin:$PATH
[root@master mysql]# source /etc/profile

初始化MySQL数据库

[root@master mysql]# mysql_secure_installation    
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users? [Y/n] y
 ... Success!

Disallow root login remotely? [Y/n] y
 ... Success!

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n] y
 ... Success!

Thanks for using MySQL!

Cleaning up...
[root@master mysql]#

在Master主服务器上修改 /etc/my.cnf 配置文件

[root@master ~]# vim /etc/my.cnf
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0
log-bin=mysql-bin
server-id=1
auto_increment_offset=1
auto_increment_increment=2

# 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
replicate-do-db=all

include all files from the config directory
                           

在slave从服务器上修改 /etc/my.cnf 配置文件

[root@master ~]# cat /etc/my.cnf | grep -v "^#"
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0
log-bin=mysql-bin
server-id=2
auto_increment_offset=2
auto_increment_increment=2

# 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
master-host=192.168.203.224
master-user=chd
master-pass=123456
master-port=3306
master-connect-retry=60
replicate-do-db=all
​
include all files from the config directory

重启主从MySQL数据库

[root@master ~]# service mysqld restart    重启MySQL数据库
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@master ~]#

[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@slave ~]# 

在Master 数据库中执行如下命令：

[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 Source distribution
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
MySQL [(none)]> grant replication slave on *.* to 'chd'@'%' identified by '123456';
Query OK, 0 rows affected (0.09 sec)
​
MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
​
MySQL [(none)]> quit
Bye

在slave数据库中指定Master IP和同步的pos点：

[root@slave mysql]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19-log Source distribution
​
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> change master to master_host='192.168.203.224',master_user='chd',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
​
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
​
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.203.224
                  Master_User: chd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   # 一个为IO线程，这里状态要为 YES
            Slave_SQL_Running: Yes   # 一个为SQL线程，这里状态要为YES
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 825
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 1
............省略部分内容...............
1 row in set (0.00 sec)
​
mysql>

在Master主服务器上配置

[root@master ~]# mysql -u root -p123456
...............省略部分内容..............
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      120 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
​
mysql> create database mysql_chd charset=utf8;   # 创建数据库
Query OK, 1 row affected (0.00 sec)
​
mysql> show databases;   # 查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_chd          |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
​
mysql> use mysql_chd;     # 切换数据库
Database changed
mysql> create table chuid (id varchar(20),name varchar(30));  # 创建数据表 chuid
Query OK, 0 rows affected (0.00 sec)
​
mysql [mysql_chd]> show tables;   # 查看数据表
+---------------------+
| Tables_in_mysql_chd |
+---------------------+
|chuid                |
+---------------------+
1 row in set (0.00 sec)
​
mysql [mysql_chd]> insert into chuid values ("1","chuid");   # 在数据表chuid 中插入数据
Query OK, 1 row affected (0.00 sec)
​
mysql [mysql_chd]> select * from chuid;   # 查询chuid表中所有数据
+------+-------+
| id   | name  |
+------+-------+
| 1    | chuid |
+------+-------+
1 row in set (0.00 sec)
​
mysql [mysql_chd]>

在slave从服务器上配置

[root@slave ~]# mysql -u root -p123456 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_chd          |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
​
mysql> use mysql_chd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
mysql [mysql_chd]> show tables;
+---------------------+
| Tables_in_mysql_chd |
+---------------------+
| chuid                  |
+---------------------+
1 row in set (0.00 sec)
​
mysql [mysql_chd]> select * from chuid;
+------+-------+
| id   | name  |
+------+-------+
| 1    | chuid |
+------+-------+
1 row in set (0.01 sec)
​
mysql [mysql_chd]> 

Master主服务器创建的数据库信息已经同步到Slave从服务器上了