MySQL-5.6安装部署及多实例主从
一、需求
1.配置多实例数据库
2.做多实例的主从同步
3.做多台服务器的主从同步
二、MySQL-5.6二进制安装(自定义目录安装)
[root@db03 ~]# mkdir /service
[root@db03 ~]# cd /service
#卸载mariadb
[root@db03 /service]# yum remove -y mariadb-libs
#创建mysql用户
[root@db03 /service]# useradd mysql
#安装依赖
[root@db03 /service]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++
#下载
[root@db03 /service]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz
[root@db03 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.42
[root@db03 ~]# ln -s /service/mysql-5.6.42 /service/mysql
#拷贝启动文件和配置文件
[root@db03 ~]# cd /service/mysql/support-files/
[root@db03 /service/mysql/support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite '/etc/my.cnf'? y
[root@db03 /service/mysql/support-files]# cp mysql.server /etc/init.d/mysqld
#初始化
[root@db03 ~]# cd /service/mysql/scripts/
[root@db03 /service/mysql/scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data
#配置systemd管理
[root@db03 ~]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
[root@db03 ~]# systemctl daemon-reload
#启动数据库
#1.使用system启动
[root@db03 ~]# systemctl start mysqld
#查看进程启动失败，没有任何报错
#2.使用mysqld启动脚本启动
[root@db03 ~]# /etc/init.d/mysqld start
/etc/init.d/mysqld: line 244: my_print_defaults: command not found
/etc/init.d/mysqld: line 264: cd: /usr/local/mysql: No such file or directory
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)'
#原因：二进制的包是源码包已经生成编译安装完成的，在cmake阶段已经指定了所有的目录都是/usr/local/mysql，所以启动时所有程序都去找/usr/local/mysql目录，没有该目录，所以启动失败
#3.解决启动问题
1）方法一：做软连接
[root@db03 ~]# ln -s /service/mysql /usr/local/mysql
2）方法二：修改启动文件
[root@db03 ~]# vim /etc/init.d/mysqld
basedir=/service/mysql
datadir=/service/mysql/data
#4.再次测试启动
[root@db03 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
#或者
[root@db03 ~]# systemctl start mysqld
#设置环境变量
[root@db03 ~]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH
[root@db02 ~]# source /etc/profile
三、配置多实例
[root@db01 ~]# cd /usr/local/
[root@db01 /usr/local]# mkdir {3307,3308,3309}
#配置多实例配置文件
[root@db01 /usr/local]# vim 3307/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/3307/data
port=3307
socket=/usr/local/3307/data/mysql.sock
server_id=7
log-error=/usr/local/3307/data/mysql.err
[root@db01 /usr/local]# cat 3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/3308/data
port=3308
socket=/usr/local/3308/data/mysql.sock
server_id=8
log-error=/usr/local/3308/data/mysql.err
[root@db01 /usr/local]# cat 3309/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/3309/data
port=3309
socket=/usr/local/3309/data/mysql.sock
server_id=9
log-error=/usr/local/3309/data/mysql.err
#初始化多实例
[root@db01 /usr/local]# cd mysql/scripts/
[root@db01 /usr/local/mysql/scripts]# ./mysql_install_db --defaults-file=/usr/local/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/3307/data
[root@db01 /usr/local/mysql/scripts]# ./mysql_install_db --defaults-file=/usr/local/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/3308/data
[root@db01 /usr/local/mysql/scripts]# ./mysql_install_db --defaults-file=/usr/local/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/3309/data
#查看是否生成文件
[root@db01 /usr/local]# tree -L 2 330*
3307
├── data
│ ├── auto.cnf
│ ├── db01.pid
│ ├── ibdata1
│ ├── ib_logfile0
│ ├── ib_logfile1
│ ├── mysql
│ ├── mysql.err
│ ├── performance_schema
│ └── test
└── my.cnf
3308
├── data
│ ├── auto.cnf
│ ├── db01.pid
│ ├── ibdata1
│ ├── ib_logfile0
│ ├── ib_logfile1
│ ├── mysql
│ ├── mysql.err
│ ├── performance_schema
│ └── test
└── my.cnf
3309
├── data
│ ├── auto.cnf
│ ├── db01.pid
│ ├── ibdata1
│ ├── ib_logfile0
│ ├── ib_logfile1
│ ├── mysql
│ ├── mysql.err
│ ├── performance_schema
│ └── test
└── my.cnf
[root@db01 /usr/local]# chown -R mysql.mysql 330*
#启动多实例
[root@db01 ~]# mysqld_safe --defaults-file=/usr/local/3307/my.cnf &
[root@db01 ~]# mysqld_safe --defaults-file=/usr/local/3308/my.cnf &
[root@db01 ~]# mysqld_safe --defaults-file=/usr/local/3309/my.cnf &
#停止多实例
[root@db01 ~]# mysqladmin -S /usr/local/3307/data/mysql.sock shutdown
#验证启动
[root@db01 /usr/local]# netstat -lntp | grep 330
tcp6 0 0 :::3306 :::* LISTEN 7542/mysqld
tcp6 0 0 :::3307 :::* LISTEN 8429/mysqld
tcp6 0 0 :::3308 :::* LISTEN 7876/mysqld
tcp6 0 0 :::3309 :::* LISTEN 8040/mysqld
#登录验证多实例
[root@db01 ~]# mysql -uroot -S /usr/local/3307/data/mysql.sock -e 'show variables like "server_id"'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db01 ~]# mysql -uroot -S /usr/local/3308/data/mysql.sock -e 'show variables like "server_id"'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db01 ~]# mysql -uroot -S /usr/local/3309/data/mysql.sock -e 'show variables like "server_id"'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
#多实例设置密码
[root@db01 ~]# mysqladmin -uroot password -S /usr/local/3307/data/mysql.sock
New password: 123
Confirm new password: 123
[root@db01 ~]# mysqladmin -uroot password -S /usr/local/3308/data/mysql.sock
New password: 123
Confirm new password: 123
[root@db01 ~]# mysqladmin -uroot password -S /usr/local/3309/data/mysql.sock
New password: 123
Confirm new password: 123
#配置简单连接数据库多实例
[root@db01 ~]# cat /usr/bin/mysql3307
mysql -uroot -p123 -S /usr/local/3307/data/mysql.sock
[root@db01 ~]# cat /usr/bin/mysql3308
mysql -uroot -p123 -S /usr/local/3308/data/mysql.sock
[root@db01 ~]# cat /usr/bin/mysql3309
mysql -uroot -p123 -S /usr/local/3309/data/mysql.sock
[root@db01 ~]# chmod +x /usr/bin/mysql*
#直接使用命令连接
[root@db01 ~]# mysql3307
[root@db01 ~]# mysql3308
[root@db01 ~]# mysql3309
四、数据库多实例主从
1.配置主库
#配置主库的binlog
[root@db01 /usr/local]# cat 3307/my.cnf
..... .......
log_bin=/usr/local/3307/data/mysql-bin
[root@db01 /usr/local]# mysqladmin -uroot -p123 -S /usr/local/3307/data/mysql.sock shutdown
[root@db01 /usr/local]# mysqld_safe --defaults-file=/usr/local/3307/my.cnf &
#主库授权主从的用户
[root@db01 /usr/local]# mysql3307
mysql> grant replication slave on *.* to zzc@'localhost' identified by '123';
Query OK, 0 rows affected (0.07 sec)
#主库查看binlog信息
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-sbin.000001 | 736 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.配置从库同步主库
[root@db01 /usr/local]# mysql3308
mysql> change master to
-> master_host='127.0.0.1',
-> master_port=3307,
-> master_user='zzc',
-> master_password='123',
-> master_log_file='mysql-sbin.000001',
-> master_log_pos=736;
#启动主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看主从状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: zzc
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-sbin.000001
Read_Master_Log_Pos: 736
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysql-sbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 ~]# mysql3309
mysql> change master to
-> master_host='127.0.0.1',
-> master_port=3307,
-> master_user='zzc',
-> master_password='123',
-> master_log_file='mysql-sbin.000001',
-> master_log_pos=1120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: zzc
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-sbin.000001
Read_Master_Log_Pos: 1120
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysql-sbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
