mysql主从搭建

最新推荐文章于 2024-04-18 11:27:19 发布
最新推荐文章于 2024-04-18 11:27:19 发布
阅读量108 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39381847/article/details/131895070
版权

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了

qq_39381847
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 一键搭建主从
太多的虚幻
05-26 1278
1.安装Mysql Utilities # wget -c http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.3.zip # unzip mysql-utilities-1.5.3.zip # cd mysql-utilities-1.5.3 # python ./setup.py build # pyth...
Linux下部署MySQL-5.7.35
system_All的博客
11-29 2015
Linux下部署MySQL-5.7.35 一、安装依赖 CentOS6或redHat6版本下载地址。 http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm CentOS7或redHat7版本下载地址。 http://mirror.centos.org/centos/7/os/x86_64/Packages/libaio-0.3.109-13.el7.x86_64.rpm 检查
mysql5.7一主多从配置
蛋疼的NICK
02-07 1828
之前在文章中已经介绍过如何进行配置，当时过于仓促也就没有介绍的很明确，这里复盘下重新给大家记录下一主多从的配置和复制延迟如何处理。 这里使用一台服务器进行了多实例的安装【此方式只用于学习和测试，生产环境请使用多mysql服务】 mysql5.7 安装 这里就不多介绍了，可参照此博客进行yum安装https://blog.csdn.net/wohiusdashi/article/details/89358071 新建4个数据库实例数据存储目录 /www/server/mysqldata/330
Docker Mysql 5.7搭建主从复制 CentOS7
最新发布
爱喝星冰乐的博客
04-18 509
Docker Mysql 5.7搭建主从复制 CentOS7
mysql5.7主从搭建
毛瞌羊的博客
03-21 402
## 1. 主从原理 ## 2. 配置 ### 2.1 Master节点配置`/etc/my.cnf`
MySQL5.7主从搭建
xiongfeng_im的博客
05-22 364
关闭防火墙 安装wget工具 下载mysql安装源并解压 安装mysql安装源 安装mysql服务 启动mysqlL服务并设置开机自启 mysql安装完成之后，会在/var/log/mysqld.log文件中给root生成了一个临时的默认密码 复制后面的密码，使用此密码登录 修改密码 首先，修改validate_password_policy参数的值 修改密码的长度 修改密码(要用大写) 修改master配置文件 在master数据库做一个授权(授权的密码需要是复杂的密码 不然会报错
MySQL 5.7 主从数据库搭建
Astray
05-31 3357
MySQL 5.7 主从数据库搭建MySQL 57 主从数据库搭建 数据库安装 主数据库配置 从数据库配置1.数据库安装安装MySQL5.7，主数据库为192.168.2.221，从数据库为192.168.2.222 sudo apt-get install mysql-server 2.主数据库配置 修改/etc/mysql/my.cnf #master [mysqld]
mysql5.7搭建主从数据库
qq_40840645的博客
08-23 306
mysql5.7搭建主从数据库 我们首先需要两个环境的数据库； 假设我们有一下两个数据库： 192.168.1.1（主） 192.168.1.2（从） 第一步：在主数据库的my.cnf文件中添加以下东西 my.cnf在不同的数据库版本，或者不同的安装方式可能会有不同的目录，我的是在etc这个目录下： server-id：这个是唯一的，且不能和从数据库相同，一般设置成IP的末尾数字即可。 binlog-do-db：这个是你的需要同步的数据库名称，需要记住，待会有用的。 replicate-igone-db：需
Mysql 5.7 主从复制（一主一从）搭建
淮右布衣的博客
03-06 481
Mysql 5.7 主从复制（一主一从）搭建
mysql主从搭建过程
10-31
mysql主从搭建过程
mysql-5.7.35-1.el7.x86_64.rpm-bundle.tar.zip
09-26
mysql-5.7.35-1.el7.x86_64.rpm-bundle.tar
mysql-5.7.22-el7-x86_64.tar.gz mysql-5.7.22安装包下载
06-27
mysql-5.7.22下载，此为mysql-5.7.22 压缩安装包（文件全称：mysql-5.7.22-el7-x86_64.tar.gz）
mysql主从搭建.docx
08-30
MySQL 主从搭建详解 MySQL 主从搭建是一种常见的数据库架构模式，它可以提高数据库的读取性能和可用性。下面对 MySQL 主从搭建的知识点进行详细的讲解。 环境准备 在开始搭建 MySQL 主从架构之前，需要准备两台...
MySQL主从数据库搭建方法详解
09-09
MySQL主从数据库搭建方法详解 MySQL主从数据库的搭建是一个重要的数据备份和高可用性解决方案。主从复制机制使得主数据库的数据变化能够实时地同步到从数据库，从而保证了数据的安全性和一致性。以下是一个详细的...
MySql 主从搭建配置
03-28
自己手动配置Mysql主从服务器的步骤及方法的记录笔记。 https://www.tanshuyi.top/web/blog?id=36
MySQL（V5.7）主从搭建
weixin_38597669的博客
01-23 1005
本文环境：window10 ,mysql:5.7.28;主要介绍了本人在一台计算机上进行主从搭建过程中遇到的一些坑。
mysql5.7配置主从
weixin_54104864的博客
03-03 1168
MySQL主从复制是通过日志同步的方式,实现主库数据库修改同步到从库数据库。它可以用于读写分离、负载均衡、故障恢复等场景。以上内容系统性地介绍了MySQL主从配置的全过程,可作为主从实践参考。此次通过mysqldump+binlog日志方式实现了MySQL主从配置,支持读写分离和故障恢复。从库配置变更点同步主库;断线后自动同步、binlog错误处理、SQL线程报错等问题解决办法。查看Slave状态变量是否一致,在从库查看是否同步新增数据。启动从库IO、SQL线程;5. 恢复备份到从库;
Mysql5.7-主从复制搭建步骤
热门推荐
qq_42875895的博客
09-27 11万+
一、概述 主从复制可以实现对数据库备份和读写分离： 为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务. MySQL提供了主从复制功能以提高服务的可用性与数据的安全可靠性. 主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从，但是并没有强制，也就
mysql 主从搭建
07-28
MySQL主从复制是一种常用的数据库复制技术，用于实现数据的备份、读写分离和负载均衡。下面是一种简单的MySQL主从搭建步骤： 1. 确保主服务器和从服务器上都安装了MySQL数据库，并且版本一致。 2. 在主服务器上编辑MySQL配置文件（my.cnf），启用二进制日志功能。在配置文件中添加以下内容： ``` [mysqld] log-bin=mysql-bin server-id=1 ``` `log-bin`参数指定二进制日志文件的名称，`server-id`参数指定主服务器的唯一ID。 3. 重启主服务器使配置生效。 4. 在主服务器上创建一个用于复制的用户，并授予适当的权限。例如，可以使用以下命令创建用户并授予权限： ``` CREATE USER 'replication_user'@'slave_ip' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_ip'; FLUSH PRIVILEGES; ``` `slave_ip`是从服务器的IP地址，`password`是用户的密码。 5. 在主服务器上执行以下命令，获取当前二进制日志文件和位置的信息： ``` SHOW MASTER STATUS; ``` 记下输出结果中的文件名和位置信息，稍后在从服务器上配置时会使用。 6. 在从服务器上编辑MySQL配置文件（my.cnf），启用复制功能。在配置文件中添加以下内容： ``` [mysqld] server-id=2 ``` `server-id`参数指定从服务器的唯一ID，与主服务器不同即可。 7. 重启从服务器使配置生效。 8. 在从服务器上执行以下命令，配置从服务器连接到主服务器并开始复制： ``` CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='master_log_file', MASTER_LOG_POS=master_log_pos; ``` `master_ip`是主服务器的IP地址，`replication_user`和`password`是之前创建的复制用户的用户名和密码，`master_log_file`和`master_log_pos`是之前在主服务器上获取的二进制日志文件和位置信息。 9. 启动从服务器上的复制进程： ``` START SLAVE; ``` 10. 在从服务器上执行以下命令，检查复制状态： ``` SHOW SLAVE STATUS\G ``` 如果输出结果中的`Slave_IO_Running`和`Slave_SQL_Running`都为`Yes`，表示主从复制已成功搭建。 这只是一个简单的MySQL主从搭建步骤，实际环境中可能还需要考虑更多因素，如网络配置、防火墙设置等。具体操作还需要根据实际情况进行调整。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值