Mysql高可用高性能存储应用系列3 - mysqld_multi配置主从集群|每日信息

概述

主从复制要解决的问题，1)写操作锁表，影响读操作，影响业务。2)数据库备份。3)随着数据增加，I/O操作增多，单机出现瓶颈。

主从复制就是从服务器的主节点，复制到多个从节点，默认采用异步的方式。

(资料图片仅供参考)

主从复制原理

从库服务器会开启两个一部的独立线程，I/O线程和Sql进程。

源码安装Mysql

使用cmake安装mysql-5.7.37，Linux系统是centos7，话不多说，我们开始吧，安装之前的准备工作特别重要。

1.卸载系统自带mysql

# rpm -qa | grep mysql# rpm -qa | grep mariadbmariadb-libs-5.5.60-1.el7_5.x86_64# rpm -e --nodeps mariadb*# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64

2.添加mysql禁止登录的用户及目录规划

useradd -M -s /sbin/nologin mysql

# 软件目录(为mysql安装目录创建软链接或改目录名)# ln -s  /usr/local/mysql-5.7.37 /usr/local/mysql# 数据目录# mkdir /usr/local/mysql-5.7.37/data# 日志目录# mkdir /var/log/mysql# touch /var/log/mysql/mysqld.log# chown -R mysql.mysql /var/log/mysql/mysqld.log# 其他目录（）# mkdir /usr/local/mysql-5.7.37/tmp# chown -R mysql.mysql /usr/local/mysql-5.7.37

3.下载、解压源码包,下载boost软件

boots 一组C++ 库的集合,提供了线性代数、伪随机数生成、多线程、图像处理、正则表达式、单元测试等功能。

# wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz# tar xf boost_1_59_0.tar.gz -C /usr/local

4.安装编译前的依赖包

# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel libstdc++*  libtool lrzsz  psmisc

5.进行cmake编译mysql源文件

make后的安装时间比较长，我的电脑执行了将近一个半小时，下面是安装时参数说明：

DCMAKE_INSTALL_PREFIX：指定MySQL程序的安装目录，默认/usr/local/mysqlDEFAULT_CHARSET：指定服务器默认字符集，默认latin1DWITH_EXTRA_CHARSET=all：支持扩展字符集DEFAULT_COLLATION：指定服务器默认的校对规则，默认latin1_general_ciDENABLED_LOCAL_INFILE：可以使用load data infile命令从本地导入文件DWITH_READLINE=1 使用readline功能，即方便在命令行复制、粘贴命令。DWITH_xxx_STORAGE_ENGINE：指定静态编译到mysql的存储引擎，MyISAM，       MERGE，MEMBER以及CSV四种引擎默认即被编译至服务器，不需要特别指定。DWITH_MYISAM_STORAGE_ENGINE=1 ：添加MYISAM存储引擎支持DWITH_INNOBASE_STORAGE_ENGINE=1 ：添加INNOBASE存储引擎支持DWITH_MEMORY_STORAGE_ENGINE=1 : 添加MEMORY存储引擎支持DSYSCONFDIR：初始化参数文件目录DMYSQL_DATADIR：数据文件目录DMYSQL_TCP_PORT：服务端口号，默认3306DMYSQL_UNIX_ADDR：socket文件路径，默认/tmp/mysql.sock

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.37 \-DMYSQL_DATADIR=/usr/local/mysql-5.7.37/data \-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.7.37/tmp/mysql.sock \-DDOWNLOAD_BOOST=1 \-DWITH_BOOST=/usr/local/boost_1_59_0 \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITH_ZLIB=bundled \-DWITH_SSL=system \-DENABLED_LOCAL_INFILE=1 \-DWITH_EMBEDDED_SERVER=1 \-DENABLE_DOWNLOADS=1 \-DWITH_DEBUG=0

make && make install 

配置Mysql

1.初始化mysql

/usr/local/mysql/bin/mysqld --initialize --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

初始密码 A temporary password is generated for root@localhost: T>qxtIjrX8ap

2.初始化3307、3308

/usr/local/mysql/bin/mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3307 --user=mysql --explicit_defaults_for_timestamp

/usr/local/mysql/bin/mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3308 --user=mysql --explicit_defaults_for_timestamp

3.将mysql命令加入环境变量

在/etc/profile最后加入环境变量命令,最后执行source /etc/profile，使得配置生效

export PATH=$PATH:/usr/local/mysql/bin

4.my.cnf 配置文件

[mysqld_multi]mysqld=/usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser=rootlog=/usr/local/mysql/mysql_multi.log[mysqld3307]basedir=/usr/local/mysqldatadir=/usr/local/mysql/data3307port=3307user=mysqlsocket=/tmp/mysql.sock3307server_id=3307log_bin=mysql-bin[mysqld3308]basedir=/usr/local/mysqldatadir=/usr/local/mysql/data3308port=3308user=mysqlsocket=/tmp/mysql.sock3308server_id=3308

执行和查看

killall mysqld  [root@ff355ce173e3 mysql]# mysqld_multi start[root@ff355ce173e3 mysql]# mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3307 is runningMySQL server from group: mysqld3308 is running

登录3307

没有密码，直接回车就可以。

mysql -uroot -p -P3307 -h127.0.0.1

//查看端口mysql> show variables like "port";+---------------+-------+| Variable_name | Value |+---------------+-------+| port          | 3307  |+---------------+-------+1 row in set (0.01 sec)//查看binlog日志功能是否开启mysql> show variables like "log_bin";+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)//查看server_idmysql> show variables like "server_id";+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 3307  |+---------------+-------+1 row in set (0.00 sec)

2.创建一个复制的用户,并赋予权限,首先要执行flush privileges，不然会报错。

flush privileges;create user "copy"@"%" identified by "copy";//配置权限grant replication slave on *.* to "copy"@"%";

3.查看主库的binlog位置

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

登录3308 ,配置从库

CHANGE MASTER TOMASTER_HOST="127.0.0.1",MASTER_USER="copy",MASTER_PORT=3307,MASTER_PASSWORD="copy",MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=747;

//查看从数据库信息show slave status\G;//start slave;