mysql主从搭建
mysql安装指南
1配置准备
安装使用普通用户(本次安装统一使用mysql用户),安装包使用mysql-5.7.35-el7-x86_64.tar.gz解压版安装。
Mysql 5.7.3的安装配置 | |
---|---|
机器 | 192.168.1.11(test1)、192.168.1.12(test2) |
系统 | CentOS Linux release 7.6.1810 (Core) |
系统内核 | core-4.1-amd64:core-4.1-noarch |
安装包 | mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz |
安装路径 | /home/mysql |
安装需求 | 已关闭SElinux,已关闭防火墙 |
备注 | 安装使用普通用户(本次统一使用MySQL用户) |
2. mysql安装
2.1 软件下载
linux机器wget下载:wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.35-el7-x86_64.tar.gz
win机器浏览器/下载工具:https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.35-el7-x86_64.tar.gz
2.2 压缩包上传
fileZilla或者ftp工具上传压缩包到 /home/mysql/ 目录下
2.3 压缩包解压缩以及更名
[mysql@test1 ~]$ tar -zxvf mysql-5.7.35-el7-x86_64.tar.gz
2.4 修改启动文件
命令
[mysql@test1 support-files]$ cd /home/mysql/mysql/support-files
[mysql@test1 support-files]$ vim mysql.server
#文件修改 原文件
# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test -z "$datadir"
then
datadir=/usr/local/mysql/data
fi
sbindir=/usr/local/mysql/bin
libexecdir=/usr/local/mysql/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
#修改后
# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/home/mysql/mysql5.7.3
bindir=/home/mysql/mysql5.7.3/bin
if test -z "$datadir"
then
datadir=/home/mysql/mysql5.7.3/data
fi
sbindir=/home/mysql/mysql5.7.3/bin
libexecdir=/home/mysql/mysql5.7.3/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
2.5 初始化
[mysql@test1 mysql5.7.3]$ bin/mysqld --initialize --user=mysql --basedir=/home/mysql/mysql5.7.3 --datadir=/home/mysql/mysql5.7.3/data
2021-09-14T10:17:20.574181Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-14T10:17:20.920371Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-14T10:17:20.974696Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-14T10:17:21.034047Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f2657f22-1544-11ec-8b09-000c2938bea4.
2021-09-14T10:17:21.035010Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-14T10:17:21.945763Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-14T10:17:21.945797Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-14T10:17:21.946862Z 0 [Warning] CA certificate ca.pem is self signed.
2021-09-14T10:17:22.745792Z 1 [Note] A temporary password is generated for root@localhost: _LpSGgy9rXOi
#以上为初始化生成的随机密码
2.6 授权操作
[mysql@test1 mysql5.7.3]$ bin/mysql_ssl_rsa_setup --basedir=/home/mysql/mysql5.7.3 --datadir=/home/mysql/mysql5.7.3/data
2.7 配置mysql用户环境变量
[mysql@test1 ~]$ vim ~/.bash_profile
#文件修改前
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
修改后
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
PATH=/home/mysql/mysql5.7.3/bin:$PATH
export PATH
2.8 centos7系列删除mariadb 需要root
[root@test1 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@test1 ~]# rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
2.9 服务启停
[mysql@test1 support-files]$ pwd
/home/mysql/mysql5.7.3/support-files
[mysql@test1 support-files]$ ./mysql.server start
Starting MySQL.[ OK ]
[mysql@test1 support-files]$ mysql.server status #查看状态
[mysql@test1 support-files]$ mysql.server stop #停止mysql
2.10 配置服务自启动
[root@test1 ~]# cp /home/mysql/mysql5.7.3/support-files/mysql.server /etc/init.d/mysqld
[root@test1 ~]# vim /usr/lib/systemd/system/mysql.service
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Service]
User=mysql
Group=mysql
Type=forking
PermissionsStartOnly=false
ExecStart= /etc/init.d/mysqld start
ExecStop= /etc/init.d/mysqld stop
ExecReload= /etc/init.d/mysqld restart
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target
[root@test1 ~]# systemctl daemon-reload #重新加载一下服务的配置文件
[root@test1 ~]# systemctl start mysql.service #开启mysql服务
[root@test1 ~]# systemctl stop mysql.service #关闭mysql服务
[root@test1 ~]# systemctl enable mysql.service #配置开机自启
3.mysql数据库的配置
3.1数据库有关配置
[mysql@test1 support-files]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> SET PASSWORD=PASSWORD('123456'); #密码重置
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
[mysql@test1 support-files]$ mysql -uroot -p123456
mysql: [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 3
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
4.主从库的配置
1.另一台mysql 配置同2,3步骤
[mysql@test2 support-files]$ cd /home/mysql/mysql/support-files
[mysql@test2 support-files]$ vim mysql.server
#文件修改 原文件
# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test -z "$datadir"
then
datadir=/usr/local/mysql/data
fi
sbindir=/usr/local/mysql/bin
libexecdir=/usr/local/mysql/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
#修改后
# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/home/mysql/mysql5.7.3
bindir=/home/mysql/mysql5.7.3/bin
if test -z "$datadir"
then
datadir=/home/mysql/mysql5.7.3/data
fi
sbindir=/home/mysql/mysql5.7.3/bin
libexecdir=/home/mysql/mysql5.7.3/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
[mysql@test2 mysql5.7.3]$ bin/mysqld --initialize --user=mysql --basedir=/home/mysql/mysql5.7.3 --datadir=/home/mysql/mysql5.7.3/data
2021-09-14T11:01:52.836212Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-09-14T11:01:52.836537Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2021-09-14T11:01:52.837060Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-14T11:01:53.076230Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-14T11:01:53.134881Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-14T11:01:53.192914Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2b209518-154b-11ec-bc32-000c290007c6.
2021-09-14T11:01:53.193924Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-14T11:01:54.929055Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-14T11:01:54.929080Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-14T11:01:54.929833Z 0 [Warning] CA certificate ca.pem is self signed.
2021-09-14T11:01:55.231616Z 1 [Note] A temporary password is generated for root@localhost: +F<4cr*zf;wP
[mysql@test2 ~]$ vim ~/.bash_profile
#文件修改前
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
修改后
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
PATH=/home/mysql/mysql5.7.3/bin:$PATH
export PATH
[root@test2 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@test2 ~]# rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
[mysql@test2 support-files]$ ./mysql.server start
Starting MySQL.[ OK ]
[mysql@test2 support-files]$ pwd
/home/mysql/mysql5.7.3/support-files
[mysql@test2 support-files]$ ./mysql.server start
Starting MySQL.[ OK ]
[mysql@test2 support-files]$ mysql.server status #查看状态
[mysql@test2 support-files]$ mysql.server stop #停止mysql
[mysql@test2 support-files]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> SET PASSWORD=PASSWORD('123456'); #密码重置
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
[mysql@test2 support-files]$ mysql -uroot -p123456
mysql: [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 3
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
2 主库配置
1.mysql配置
[mysql@test2 support-files]$ mysql -uroot -p123456
mysql: [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 3
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> grant replication slave on *.* to 'repl_user'@'192.168.1.12' identified by 'repl_user';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# repl_user是我们创建专门用来备份数据库的用户
192.168.1.12是从mysql服务器的ip,注意在部署的时候要根据实际情况替换掉
identified后面的repl_user是repl_user的密码
mysql> create database taizhitech;
Query OK, 1 row affected (0.00 sec)
#taizhi用户配置远程登陆
mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_user';
Query OK, 0 rows affected (0.01 sec)
#taizhi用户配置本地登录
mysql> CREATE USER 'repl_user'@'localhost' IDENTIFIED BY 'repl_user';
Query OK, 0 rows affected (0.00 sec)
# 给taizhi用户操作taizhitech的权限:
mysql> grant all privileges on taizhitech.* to repl_user;
Query OK, 0 rows affected (0.00 sec)
# 给用户taizhi在所有登陆ip的权限:
mysql> grant all privileges on *.* to 'rep1_user'@'%' identified by 'repl_user';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#使用测试
mysql> use taizhitech
Database changed
mysql> create table TEST(id int(6),name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into TEST values(1,'wuji');
Query OK, 1 row affected (0.01 sec)
mysql> insert into TEST values(2,'zhaomin');
Query OK, 1 row affected (0.00 sec)
mysql> Select * from TEST;
+------+---------+
| id | name |
+------+---------+
| 1 | wuji |
| 2 | zhaomin |
+------+---------+
2 rows in set (0.00 sec)
2 配置文件配置
[mysql@test1 support-files]$ pwd
/home/mysql/mysql5.7.3/support-files
[mysql@test1 support-files]$ vim my-default.cnf #文末新增内容如下
[mysql@test1 support-files]$ cat my-default.cnf
log_bin=mysql-bin
binlog_format = mixed
server_id =1
read-only=0
binlog-do-db=taizhitech
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=test
auto-increment-increment=2
auto-increment-offset=1
[mysql@test1 support-files]$ cp my-default.cnf /home/mysql/mysql5.7.3/my.cnf
[mysql@test1 support-files]$ ./mysql.server restart
3 主节点状态
[mysql@test1 support-files]$ mysql -uroot -p123456
mysql: [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 2
Server version: 5.7.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 353
Binlog_Do_DB: taizhitech
Binlog_Ignore_DB: information_schema,performance_schema,mysql,test
Executed_Gtid_Set:
1 row in set (0.00 sec)
3 从库配置
[mysql@test2 support-files]$ pwd
/home/mysql/mysql5.7.3/support-files
[mysql@test2 support-files]$ vim my-default.cnf #文末新增内容如下
[mysql@test2 support-files]$ cat my-default.cnf
log-bin = mysql-bin
binlog_format = mixed
server_id = 2
replicate-do-db=eipdb
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=test
relay_log=mysqld-relay-bin
log-slave-updates = ON
[mysql@test2 support-files]$ cp my-default.cnf /home/mysql/mysql5.7.3/my.cnf
[mysql@test2 support-files]$ ./mysql.server restart
Shutting down MySQL..[ OK ]
Starting MySQL.[ OK ]
[mysql@test2 support-files]$ mysql -uroot -p123456
mysql: [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 2
Server version: 5.7.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.1.11', MASTER_USER='repl_user',
-> MASTER_PASSWORD='repl_user', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=353;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
#参数注释
## CHANGE MASTER TO
## -> MASTER_HOST='192.168.1.11',主数据库的ip,部署时根据实际情况,将其替换
## -> MASTER_USER='repl_user',备份数据库用户
## -> MASTER_PASSWORD='repl_user',备份数据库密码
## -> MASTER_LOG_FILE='mysql-bin.000001', 主库show master status\G
## -> MASTER_LOG_POS=154;主库show master status\G
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[mysql@test2 support-files]$ ./mysql.server restart
Shutting down MySQL..[ OK ]
Starting MySQL.[ OK ]
[mysql@test2 support-files]$ mysql -uroot -p123456
mysql: [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 4
Server version: 5.7.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #节点运行情况
Slave_SQL_Running: Yes #节点运行情况
Replicate_Do_DB: taizhitech
Replicate_Ignore_DB: information_schema,performance_schema,mysql,test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 528
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f2657f22-1544-11ec-8b09-000c2938bea4
Master_Info_File: /home/mysql/mysql5.7.3/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5 mysql启动失败解决方案
[mysql@test1 support-files]$ ./mysql.server start
Starting MySQL./home/mysql/mysql5.7.3/bin/mysqld_safe: line 626: /var/log/mariadb/mariadb.log: No such file or directory
2021-09-14T10:30:04.106531Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
/home/mysql/mysql5.7.3/bin/mysqld_safe: line 144: /var/log/mariadb/mariadb.log: No such file or directory
The server quit without updating PID file (/var/lib/mysql/test1.pid).[FAILED]
出现这个的原因有很多种,下面来分析一下mysql的各个文件:
(1) mysql.server,在/home/mysql/mysql5.7.3/support-files/下。这是个脚本文件,这个脚本的主要作用就是为了方便启动和关闭mysql服务。它包含mysqld和mysqld_safe,这两者都可以用来启动脚本。
(2) 日志文件,在/home/mysql/mysql5.7.3/data/下,以hostname.err命名,本例为mysql@test1.err。
(3) sock文件,在/tmp/目录下,有mysql.sock和mysql.sock.lock两个文件。
mysql.sock.lock里面是mysql的进程号。重启mysql就会自动更新或者创建。
mysql.sock,这是本机启动mysql需要的文件。一般重启mysql就会自动创建。这个文件不存在,mysql可以启动,但是进入mysql命令行客户端会报错:# mysql 出错ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql.sock'。
(4) pid文件,在/home/mysql/mysql5.7.3/data/下,以hostname.pid命名,本例为mysql@test1.pid。里面存放的是mysql的进程号。启动mysql就会自动创建,关闭mysql就会消失。只要没有启动起来,pid文件就不存在,就会报上面的错误。
(5) my.cnf配置文件,一般在/etc/下。
启动mysql时,它首先会依次寻找my.cnf这个配置文件。(若都没有my.cnf文件,会另外自动寻找别的配置文件)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
如上所示,它会先寻找/etc,再找/etc/mysql,再找/user/local/mysql,再找本地安装目录下的my.cnf。并且它的优先级是递增的,/etc最低,本地安装目录最高,优先级高的my.cnf会覆盖优先级低的my.cnf。
总结一下,解决这个问题的几种方法。
(1)My.cnf引起的问题
若电脑之前安装过别的版本的mysql,它在其他地方生成了my.cnf文件,而mysql5.7.3这个版本并不会自动生成my.cnf文件,这就会导致它启动时还会用之前的my.cnf文件,所以无法启动。解决这个办法,有两种途径。1是把my.cnf文件全部删除,2是在优先级最高的本地安装目录建一个新的my.cnf文件。这里现在比较简单的第二种解决方法。
解决方法:
[mysql@test1 support-files]$ pwd
/home/mysql/mysql5.7.3/support-files
[mysql@test1 support-files]$ cp my-default.cnf /home/mysql/mysql5.7.3/my.cnf
[mysql@test1 support-files]$ cd ..
[mysql@test1 support-files]$ vim my.cnf
#文件修改 原文件
# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test -z "$datadir"
then
datadir=/usr/local/mysql/data
fi
sbindir=/usr/local/mysql/bin
libexecdir=/usr/local/mysql/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
#修改后
# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir/home/mysql/mysql5.7.3
bindir=/home/mysql/mysql5.7.3/bin
if test -z "$datadir"
then
datadir=/home/mysql/mysql5.7.3/data
fi
sbindir=/home/mysql/mysql5.7.3/bin
libexecdir=/home/mysql/mysql5.7.3/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
# log-error是错误日志存放的位置,系统默认放在/home/mysql/mysql5.7.3/data下的test1.err。
# pid-file是存放进程号的文件系统默认会在启动时在/home/mysql/mysql5.7.3/data目录下自动创建一个test1.pid。
[mysqld_safe]
log-error=
pid-file=
#保存退出 重启mysql
(2)多余的mysql进程引起的问题
[mysql@test1 support-files]$ ps -ef|grep mysqld
mysql 26286 1 0 18:34 pts/0 00:00:00 /bin/sh /home/mysql/mysql5.7.3/bin/mysqld_safe --datadir=/home/mysql/mysql5.7.3/data --pid-file=/home/mysql/mysql5.7.3/data/test1.pid
mysql 26377 26286 0 18:34 pts/0 00:00:01 /home/mysql/mysql5.7.3/bin/mysqld --basedir=/home/mysql/mysql5.7.3 --datadir=/home/mysql/mysql5.7.3/data --plugin-dir=/home/mysql/mysql5.7.3/lib/plugin --log-error=test1.err --pid-file=/home/mysql/mysql5.7.3/data/test1.pid
mysql 27610 26241 0 18:47 pts/0 00:00:00 grep --color=auto mysqld
[mysql@test1 support-files]$ Kill -9 26377
(3)sock文件引起的问题。
启动mysql时,mysql.sock和mysql.sock.lock没有及时更新,需要使用命令:rm -rf mysql.sock,rm -rf mysql.sock.lock。然后再启动mysql服务,它就会及时生成新的sock文件。
(4)mysql-bin.index引起的问题。
去mysql的数据目录/data看看,如果存在mysql-bin.index,就赶快把它删除掉。可能是第二次在机器上安装mysql,有残余数据影响了服务的启动。
6 主从配置失败
在从数据库配置时:
输入命令:show slave status\G,执行后看到Slave_IO_Running和Slave_SQL_Running没有为Yes。那表示没有配置成功(废话)。
我遇到的情况是这样:
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
解决方法:
(1)先stop slave,停掉slave服务
(2)到主服务器上查看主机状态:
记录File和Position对应的值。
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1593
Binlog_Do_DB: taizhitech
Binlog_Ignore_DB: information_schema,performance_schema,mysql,test
Executed_Gtid_Set:
1 row in set (0.00 sec)
(3)到slave服务器上执行手动同步:
mysql> change master to
> master_host='192.168.219.119',
> master_user='repl_user',
> master_password='repl2016',
> master_port=3306,
> master_log_file='mysql-bin.000002',
> master_log_pos=3153;
(4)再次查看slave状态发现:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
不过也有可能出现:
mysql> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: Yes
解决方法:
(1)在主数据库的命令行客户端:看一下是否授权给
mysql> show grants for 'repl_user'@'192.168.1.12';
+---------------------------------------------------------------+
| Grants for repl_user@192.168.1.12 |
+---------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.12' |
+---------------------------------------------------------------+
(2)根据之上的结果。已经赋予了权限。
[mysql@test2 ~]$ mysql -uroot -h 192.168.1.11 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'test2' (using password: YES)
[mysql@test2 ~]$ mysql -urepl_user -h 192.168.1.11 -prepl_user
mysql: [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 5
Server version: 5.7.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> exit
Bye
(3)好像也登陆进去了。于是再一次执行了第一个的解决方法,先停掉slave,再设置,再重启数据库。
(4)于是在查看从数据库的状态,都是yes了
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章