MySQL Standalone
info
# MySQL info
version: 5.7.34
ip: 192.101.11.68
user: root
password: hldb<7/admin
data: /opt/data/mysql
start: service mysql start
stop: service mysql stop
install path: /usr/local/mysql
download
https://downloads.mysql.com/archives/community/
select a version for Linux:
5.7.34
Linux Generic x86,64-bit
get file: mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
install：
https://dev.mysql.com/doc/refman/5.7/en/postinstallation.html
https://dev.mysql.com/doc/refman/5.7/en/option-files.html
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
cd /usr/local
tar zxvf /path/to/mysql-VERSION-OS.tar.gz
ln -s full-path-to-mysql-VERSION-OS mysql
cd mysql
mkdir mysql-files
chown mysql:mysql mysql-files
chmod 750 mysql-files
prepare the db data file
mkdir -p /opt/data/mysql
chown mysql:mysql -R /opt/data/mysql
configuration
vi /etc/my.cnf
[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/opt/data/mysql
log-error=/opt/data/mysql/mysql.err
pid-file=/opt/data/mysql/mysql.pid
socket=/tmp/mysql.sock
symbolic-links=0
lower_case_table_names=1
character-set-server=utf8mb4
explicit_defaults_for_timestamp=true
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
initialize
chmod 755 /usr/local/mysql/bin/*
/usr/local/mysql/bin/mysqld --initialize --user=mysql
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
chown -R mysql:mysql /var/log/mariadb/
/usr/local/mysql/bin/mysql_ssl_rsa_setup
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod 755 /etc/init.d/mysql
vi /etc/profile
add below:
export PATH=$PATH:/usr/local/mysql/bin/
and
source /etc/profile
start
service mysql start
change the password after the first startup.
mysql -u root -p
input the secret which can getting from log-error at /opt/data/mysql/mysql.err
2021-07-21T13:23:07.264112Z 1 [Note] A temporary password is generated for root@localhost: <i93<taoW/Tg
execute sql command:
# change password
SET PASSWORD = PASSWORD('hldb<7/admin');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES;
# open remote connection
use mysql
update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;
stop
service mysql stop
MySQL Master/Slave Cluster
info
# master MySQL info
version: 5.7.34
ip: 192.101.11.68
user: root
password: hldb<7/admin
data: /opt/data/mysql
start: service mysql start
stop: service mysql stop
install path: /usr/local/mysql
# slave MySQL info
version: 5.7.34
ip: 192.101.11.71
user: root
password: hldb<7/admin
data: /opt/data/mysql
start: service mysql start
stop: service mysql stop
install path: /usr/local/mysql
install
on master mysql
vim /etc/my.cnf
# add properties
[mysqld]
log-bin=mysql-bin
server-id=1000
then
service mysql restart
# login mysql to check the master status
mysql -u root -p
show master status
#get the file name: mysql-bin.000001, and file postion
# reset file position, default is 154
reset master;
show variables like '%server_id%';
#check the server_id is 1000
# create a new user for slave mysql copying data.
mysql -u root -p
CREATE USER 'shadower'@'%'IDENTIFIED BY 'hldb<7/admin';
GRANT ALL PRIVILEGES ON *.* TO 'shadower'@'%';
grant ALL PRIVILEGES on *.* to shadower@'localhost' identified by 'hldb<7/admin';
FLUSH PRIVILEGES;
select host,user from mysql.user;
on slave mysql
vim /etc/my.cnf
# add properties
[mysqld]
log-bin=mysql-bin
server-id=1001
# set master info for copy
mysql -u root -p
# the value of master_log_pos must be the same to the master file postion.
change master to master_host='192.101.11.68',master_user='shadower',
master_port=3306,master_password='hldb<7/admin',
master_log_file='mysql-bin.000001',master_log_pos=154;
# start slave
mysql -u root -p
start slave;
optional
在建立 slave 之前，如果 master 已经有一些数据库存在，进行一次初始导入数据库到 slave
# on master
mysqldump -uroot -p --all-databases > dbdump.db
scp dbdump.db root@192.101.11.71:/opt/tmp/
# on slave
rm -rf /opt/data/mysql/auto.cnf
service mysql restart
mysql -uroot -p
stop slave;
source dbdump.db;
# the value of master_log_pos must be the same to the master file postion.
change master to master_host='192.101.11.68',master_user='shadower',
master_port=3306,master_password='hldb<7/admin',
master_log_file='mysql-bin.000001',master_log_pos=154;
start slave;
show slave status\G;
# slave is ok.
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes
如果 master 新增加了一个数据库，需要把新增的数据库导入到 slave，然后还能够进行同步复制
# on slave
mysql -uroot -p
stop slave;
# on master
mysqldump -uroot -p --master-data --single-transaction -R --databases thenewdbname > thenewdb.sql;
scp thenewdb.sql root@192.101.11.71:/opt/tmp/
# on slave
mysql -uroot -p < thenewdb.sql
mysql -uroot -p
start slave;
如果从服务器不能复制数据，报错信息为：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
说明主从所用的二进制复制文件名不一致，需要重新设置 slave 的同步文件
参照 MySQL验证主从同步报‘Could not find first log file name in binary log index file‘错误的解决办法-CSDN博客
# on slave
change master to master_host='192.101.11.68',master_user='shadower',
master_port=3306,master_password='hldb<7/admin',
master_log_file='mysql-bin.000003',master_log_pos=154;
# 也可以在 master 端重新生成一个同步的二进制文件，slave 端使用此新的文件
# on master
mysql -uroot -p
flush logs;
show master status\G;
// File: mysql-bin.000003 Position: 154
MySQL Master/Slave Cluster(Docker)
info-test
# master MySQL info
version: 5.7.34
ip: 192.101.11.70
user: root
password: hldb<7/admin
data: /opt/data/mysql
install path: /opt/mysql5
# slave MySQL info
version: 5.7.34
ip: 192.101.11.69
user: root
password: hldb<7/admin
data: /opt/data/mysql
install path: /opt/mysql5
download
docker pull mysql:5.7.34
install
on master mysql
mkdir -p /opt/data/mysql
mkdir -p /opt/data/mysqlcnf
vim /opt/data/mysqlcnf/my.cnf
# add properties
[mysqld]
log-bin=mysql-bin
server-id=1000
character-set-server=utf8mb4
explicit_defaults_for_timestamp=true
lower_case_table_names=1
[client]
default-character-set=utf8
then
docker run -d -p 13306:3306 --name mysqlhlapp-master -v /opt/data/mysql:/var/lib/mysql -v /opt/data/mysqlcnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD='hldb<7/admin' --restart unless-stopped mysql:5.7.34
# login mysql to check the master status
docker exec -it mysqlhlapp-master sh
mysql -u root -p
show master status
#get the file name: mysql-bin.000001, and file postion
# reset file position, default is 154
reset master;
show variables like '%server_id%';
#check the server_id is 1000
# create a new user for slave mysql copying data.
docker exec -it mysqlhlapp-master sh
mysql -u root -p
CREATE USER 'shadower'@'%'IDENTIFIED BY 'hldb<7/admin';
GRANT ALL PRIVILEGES ON *.* TO 'shadower'@'%';
grant ALL PRIVILEGES on *.* to shadower@'localhost' identified by 'hldb<7/admin';
FLUSH PRIVILEGES;
select host,user from mysql.user;
on slave mysql
mkdir -p /opt/data/mysql
mkdir -p /opt/data/mysqlcnf
vim /opt/data/mysqlcnf/my.cnf
# add properties
[mysqld]
log-bin=mysql-bin
server-id=1001
character-set-server=utf8mb4
explicit_defaults_for_timestamp=true
lower_case_table_names=1
[client]
default-character-set=utf8
then
docker run -d -p 13306:3306 --name mysqlhlapp-slave -v /opt/data/mysql:/var/lib/mysql -v /opt/data/mysqlcnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD='hldb<7/admin' --restart unless-stopped mysql:5.7.34
# set master info for copy
docker exec -it mysqlhlapp-slave sh
mysql -u root -p
# the value of master_log_pos must be the same to the master file postion.
change master to master_host='192.101.11.70',master_user='shadower',
master_port=13306,master_password='hldb<7/admin',
master_log_file='mysql-bin.000001',master_log_pos=154;
# start slave
docker exec -it mysqlhlapp-slave sh
mysql -u root -p
start slave;
start
docker run -d -p 13306:3306 --name mysqlhlapp-master -v /opt/data/mysql:/var/lib/mysql -v /opt/data/mysqlcnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD='hldb<7/admin' --restart unless-stopped mysql:5.7.34
docker run -d -p 13306:3306 --name mysqlhlapp-slave -v /opt/data/mysql:/var/lib/mysql -v /opt/data/mysqlcnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD='hldb<7/admin' --restart unless-stopped mysql:5.7.34
stop
docker stop mysqlhlapp-master;
docker rm mysqlhlapp-master;
docker stop mysqlhlapp-slave;
docker rm mysqlhlapp-slave;
restart
# enter into the docker containter to stop slave first
stop slave
mysqladmin shutdown -u用户名 -p密码
docker stop mysqlhlapp-slave
docker rm mysqlhlapp-slave
# then enter into the docker containter to stop master
mysqladmin shutdown -u用户名 -p密码
docker stop mysqlhlapp-master
docker rm mysqlhlapp-master
# start the master container first, and enter into it.
show master status\G
##get the file name: mysql-bin.000004, and file postion
# then start salve container
# enter into the docker containter to start slave at last
reset slave;
# the value of master_log_pos must be the same to the master file postion.
change master to master_host='192.101.11.70',master_user='shadower',
master_port=13306,master_password='hldb<7/admin',
master_log_file='mysql-bin.000004',master_log_pos=154;
start slave;
show slave status\G