主从复制要解决的问题,1)写操作锁表,影响读操作,影响业务。2)数据库备份。3)随着数据增加,I/O操作增多,单机出现瓶颈。
主从复制就是从服务器的主节点,复制到多个从节点,默认采用异步的方式。
从库服务器会开启两个一部的独立线程,I/O线程和Sql进程。
使用cmake安装mysql-5.7.37,Linux系统是centos7,话不多说,我们开始吧,安装之前的准备工作特别重要。
1.卸载系统自带mysql
# rpm -qa | grep mysql
# rpm -qa | grep mariadb
mariadb-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/mysql
DEFAULT_CHARSET:指定服务器默认字符集,默认latin1
DWITH_EXTRA_CHARSET=all:支持扩展字符集
DEFAULT_COLLATION:指定服务器默认的校对规则,默认latin1_general_ci
DENABLED_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:服务端口号,默认3306
DMYSQL_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
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_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=root
log=/usr/local/mysql/mysql_multi.log
[mysqld3307]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3307
port=3307
user=mysql
socket=/tmp/mysql.sock3307
server_id=3307
log_bin=mysql-bin
[mysqld3308]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3308
port=3308
user=mysql
socket=/tmp/mysql.sock3308
server_id=3308
执行和查看
killall mysqld
[root@ff355ce173e3 mysql]# mysqld_multi start
[root@ff355ce173e3 mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
没有密码,直接回车就可以。
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_id
mysql> 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)
CHANGE MASTER TO
MASTER_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;
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。