Mysql5.7安装错误处理与主从同步及!

时间:2021-04-20 04:52:18

basedir=/iddbs/mysql-5.7.16

datadir=/iddbs/mysql5.7/data3306

 

 

一、自定义Mysql.5.7版本免编译安装: 

1、Db-server1安装前期准备

 

前期准备先确认是否已经安装了Mysql,如果有rpm安装会影响面编译安装:

具体方法如下:

确保是否有已经rpm安装了Mysql

[root@bogon tool]#rpm -qa | grep mysql

mysql-community-libs-5.7.16-1.el6.x86_64

mysql-community-server-5.7.16-1.el6.x86_64

mysql-community-common-5.7.16-1.el6.x86_64

mysql-community-client-5.7.16-1.el6.x86_64

如果有rpm包删除:

例如:

rpm -e mysql-community-libs-5.7.16-1.el6.x86_64 mysql-community-server-5.7.16-1.el6.x86_64 mysql-community-client-5.7.16-1.el6.x86_64 mysql-community-common-5.7.16-1.el6.x86_64 --nodeps

 

 

2、创建软件安装目录:

  [root@bogon tool]# mkdir -p /iddbs/

  将面编译安装软件包下载或者拷贝至/iddbs/

  [root@bogon iddbs]# ls

  mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz

  [root@bogon iddbs]# tar -zxf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz

  [root@bogon iddbs]# ls

  mysql-5.7.16-linux-glibc2.5-x86_64   mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz  

  

  将减压目录命名为

  [root@bogon iddbs]# mv mysql-5.7.16-linux-glibc2.5-x86_64 /iddbs/mysql-5.7.16 

  创建用户:

  [root@bogon iddbs]# useradd -s /sbin/nologin -M iddbs

  授权给iddbs用户:

  [root@bogon iddbs]# chown -R iddbs.iddbs /iddbs/

 

3、做软链接可以不做如果做了软链接 basedir=/iddbs/mysql以下所有的相关联的basedir必须是basedir=/iddbs/mysql

 

   [root@bogon iddbs]# cd /iddbs/

   [root@bogon iddbs]# ls

     mysql-5.7.16   mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz  

   [root@bogon iddbs]# ln -s mysql-5.7.16 / /iddbs/mysql

 

4、创建数据文件:

   [root@bogon iddbs# mkdir -p /iddbs/mysql5.7/data3306

 

5、初始化数据库:

   [root@bogon bin]# pwd

    /iddbs/mysql-5.7.16/bin

   [root@bogon bin]#

    执行命令mysql_install_db --basedir=/iddbs/mysql-5.7.16 --datadir=/iddbs/mysql5.7/data3306 --user=iddbs

初始化错误

2017-06-10 01:31:22 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

2017-06-10 01:31:22 [ERROR]   The data directory '/iddbs/mysql5.7/data3306' already exist and is not empty.    

需使用以下命令:

mysqld --initialize --user=iddbs --basedir=/iddbs/mysql-5.7.16 --datadir=/iddbsdata/mysql5.7/data3306

在执行安装时必须确保数据目录下为空及(/iddbsdata/mysql5.7/data3306下不含任何文件)否则报错

    

6、启动数据库常见错误及处理方法:

    [root@bogon mysql]# cp support-files/mysql.server /etc/init.d/mysqld   #将启动文件拷贝至/etc/init.d目录下以可以使用init.d启动

cp: overwrite `/etc/init.d/mysqld'? y

       

[root@bogon mysql]# cp support-files/my-default.cnf /etc/my.cnf  #将配置文件拷贝到/etc下且名字为my.cnf

[root@bogon mysql]# cat /etc/my.cnf

# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]

port=3306

user=iddbs

password ='newpassword'

socket=/iddbs/mysql5.7/data3306/mysql.sock

[mysqld]

max_connections=1000

max_user_connections=500

wait_timeout=200

user=iddbs

server-id=5

port=3306

basedir=/iddbs/mysql5.7

datadir=/iddbs/mysql5.7/data3306

socket=/iddbs/mysql5.7/data3306/mysql.sock

pid-file=/iddbs/mysql5.7/data3306/mysql.pid

log-bin=/iddbs/mysql5.7/data3306/mysql-bin

relay-log=/iddbs/mysql5.7/data3306/relay-bin

log-error=/iddbs/mysql5.7/data3306/mysql-error.log

interactive_timeout=172800

wait_timeout=172800

max_allowed_packet=16M

expire_logs_days=7

auto-increment-increment=2

auto-increment-offset=1

character-set-server=utf8

collation_server=utf8_general_ci

innodb_data_file_path=ibdata1:1G:autoextend

innodb-buffer-pool-size=15G

binlog_format=row

relay_log_recovery=1

log_bin_trust_function_creators=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

lower_case_table_names = 1

character_set_server = utf8

event_scheduler = on

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

default-character-set = utf8

 

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

 

启动报错:

[root@bogon mysql]# /etc/init.d/mysqld start

/etc/init.d/mysqld: line 276: cd: /usr/local/mysql: No such file or directory

Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

解决方法修改mysql启动文件文件目录路径:

[root@bogon mysql]# vim /etc/init.d/mysqld

 

if test -z "$basedir"

then

  basedir=/iddbs/mysql-5.7.16

  bindir=/iddbs/mysql-5.7.16/bin

  if test -z "$datadir"

  then

    datadir=/iddbs/mysql5.7/data3306

  fi

  sbindir=/iddbs/mysql-5.7.16/bin

  libexecdir=/iddbs/mysql-5.7.16/bin

else

  bindir="$basedir/bin"

报错退出再次启动:

[root@bogon mysql]# /etc/init.d/mysqld start

启动报错:

Starting MySQL ERROR! Couldn't find MySQL server (/iddbs/mysql5.7/bin/mysqld_safe)

为什么会出现上述情况,由于/etc/init.d/mysqld 启动时指定了/iddbs/mysql5.7/data3306数据文件,但在/iddbs/mysql5.7/下是不含bin的;真正的bin文件在/iddbs/mysql-5.7.16/bin下,因此可做软链接:

ln -s /iddbs/mysql-5.7.16/bin  /iddbs/mysql5.7/bin

再次启动:

[root@bogon mysql]# /etc/init.d/mysqld start

Starting MySQL.... SUCCESS!

启动服务/etc/init.d/mysqld 可使用参数

    [root@bogon data3306]# /etc/init.d/mysqld

Usage: mysqld  {start|stop|restart|reload|force-reload|status}

含启动、停止、重启、强制重启、状态

7、查看进程:

[root@bogon mysql]# lsof -i:3306

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysqld  4098 mysql   10u  IPv4  32707      0t0  TCP *:mysql (LISTEN)

[root@bogon support-files]# ps -ef | grep mysql| grep -v grep

root      1978     1  0 Jun09 pts/1    00:00:00 /bin/sh /iddbs/mysql5.7/bin/mysqld_safe --datadir=/iddbs/mysql5.7/data3306 --pid-file=/iddbs/mysql5.7/data3306/mysql.pid

iddbs     2445  1978  0 Jun09 pts/1    00:00:27 /usr/local/mysql/bin/mysqld --basedir=/iddbs/mysql5.7 --datadir=/iddbs/mysql5.7/data3306 --plugin-dir=/usr/local/mysql/lib/plugin --user=iddbs --log-error=/iddbs/mysql5.7/data3306/mysql-error.log --pid-file=/iddbs/mysql5.7/data3306/mysql.pid --socket=/iddbs/mysql5.7/data3306/mysql.sock --port=3306

至此mysql5.7安装完成;

 

二、登录mysql:

 

   [root@bogon bin]# mysql -uroot -p

   Enter password:

   提示需要输入密码;Mysql5.7登录密码隐藏在错误日志内:

   [root@bogon bin]# cd /iddbs/mysql5.7//data3306/

   [root@bogon data3306]# ls

auto.cnf        ib_logfile0  mysql             mysql-bin.000003  mysql-bin.index  mysql.pid        performance_schema

ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.000004  mysqld_safe.pid  mysql.sock       sys

ibdata1         ibtmp1       mysql-bin.000002  mysql-bin.000005  mysql-error.log  mysql.sock.lock

[root@bogon data3306]# grep password mysql-error.log

2017-06-09T13:18:12.551595Z 1 [Note] A temporary password is generated for root@localhost: &DFbeiVDs3ja   #此为此次安装密码自动生成,每次安装不一样

[root@bogon data3306]# mysql -uroot -p’&DFbeiVDs3ja ’

登录以后发现不能进行任何操作:

mysql>

解决方法:

mysql> set  password=password(‘newpassword’);

mysql> flush privileges;

创建iddbs用户及登录:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'iddbs'@'localhost' identified by 'newpassword';

mysql> flush privileges;

mysql> quit

使用iddbs用户登录:

[root@bogon data3306]# mysql -uiddbs -p’newpassword ’

注意可能有报错:

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Unknown error 1045

因为5.7基于安全考虑在命令行输入密码是不安全的所有禁止登录;可使用

[root@bogon data3306]# mysql -uiddbs -p

Enter password: password

mysql>

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.11 sec)

 

 

三、db-server2安装

   重复db-server1安装过程

四、Mysql主从同步

 

Mysql部署模式有:一主一从主从同步、一主多从、双主互为主从、双主多从、多主多从环状级联

此次部署采用双主互为主从模式:

 

 

  1. 主从复制:

a) 主从复制原理:

主从复制是由于masterbinlog记录了所有操作。利用binlog实现同步

Mysql主从复制是一个异步的复制过程(但在一般情况下感觉是实施同步的),数据库数据从一个Mysql数据库(主库master)复制到另一个Mysql数据库(从库slave)。在masterslave之间实现整个主从复制的过程是由三个线程参与完成的。其中两个线程(SQL线程和IO线程)在slave端,另外一个线程(IO线程)在master端。

要是先Mysql的主从复制,首先必须打开master端的binlogmysql-bin.XXXXXX)功能。否则无法实现主从复制。因为整个复制过程实际上就是slavemaster端获取binlog日志,然后在slave自身上以相同的顺序执行获取的binlog日志中所记录的各种操作。

 

b) 主从复制的应用:
主从服务器互为备份,主从设置可以加强数据架构的健壮性,当主出问题时,可以人工或者自动切换到从服务器上继续提供服务。
主从服务器可实现读写分离分担网站压力。

c) 主从复制具体操作:

 

  1. 主库(master)配置文件my.cnf开启log-bin

server-id       = 1

# Uncomment the following if you want to log updates

log-bin=/application/mysql/mysql-bin

可以在数据库中查看:

mysql> show variables like 'log%';  #查看主库的binlog开关是否生效(ON状态)

 

+---------------------------------+---------------------------------------------+

| Variable_name     | Value                                       |

+---------------------------------+---------------------------------------------+

| log               | OFF                                        |

| log_bin           | ON                                         |

  1. 建立主从数据账号 

    
mysql> grant replication slave on *.* to 'rep'@'172.22.2.%' identified by 'xuxuedong';     replication slave为同步权限。

    Query OK, 0 rows affected (0.00 sec)
主库授权账号解决从库连接主库的问题。

  1. 锁表数据保证同步数据一致性,
    mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

查看binlog节点位置。

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      259 |              |                  |

+------------------+----------+--------------+------------------+

        1 row in set (0.00 sec)

  1. 从库获取主库节点信息:

如果是主从单复制,从库可以不开启binlog

有两种情况从库必须打开binlog:

 1)级联同步:A->B->C那么B必须开启binlog

             2)从库做数据备份,数据库备份必须要有全备和binlog日志

[root@CentOS ~]# mysqldump -uroot -pxuxuedong -B -A --events -S /data/3306/mysql.sock  > /opt/new.mysql

[root@CentOS ~]# ls /opt/

new.mysql  rh

  1. 解锁主库:

mysql> unlock tables ;

Query OK, 0 rows affected (0.00 sec)

 

  1. 数据导入从库

 

[root@CentOS ~]# mysql -uroot -pxuxuedong   -S /data/3306/mysql.sock  < /opt/new.mysql

[root@CentOS 3306]# cat | mysql -uroot -pxuxuedong -S /data/3306/mysql.sock <<EOF

CHANGE MASTER TO

MASTER_HOST='172.22.2.237',

MASTER_PORT=3306,

 MASTER_USER='rep',

 MASTER_PASSWORD='xuxuedong',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=259;

 EOF

[root@CentOS 3306]# ls /data/3306/data/master.info

/data/3306/data/master.info

上述操作实际是将用户密码等信息写入从库的master.info中。

 

 

  1. 从库开始同步:

 

mysql> slave start;

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: 172.22.2.223

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 370

               Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 363

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           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: 370

              Relay_Log_Space: 519

              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

1 row in set (0.00 sec)

 

       完成以上主从同步已经实现。

  1. 总结主从复制的步骤:

1、需要两台数据库。

2、配置my.cnf文件,主库配置log-binserver-id参数,从库配置server-id且不能和主库以及其他从库一样;从库一般不开启log-bin功能。配置后重启服务生效。

3、登录主库增加用于连接主库同步的账号:如rep并授权replication slave同步权限。

4、登录主库,证库锁表flush tables with read lock;”(窗口不能关闭,窗口关闭及失效。)然后show  master status查看binlog的位置状态。

5、新开窗口,备份或导出原有的数据库,并拷贝到从库所在的服务器目录。如果数据量很大,并且允许停机,可停机打包,而不用mysqldump.

6、J解锁主库:unlock tables

7、把主库导出的原有的数据恢复到从库。

8、根据主库的show master status 查看binlog的位置状态,在从库执行change master语句。

9、从库开启同步开关,start slave

10、从库show slave status \G;查看同步状态,并在主库进行更行测试。

 

 

d) 忽略权限库同步:
1、主库下忽略mysql库及information-schema库的主从同步。及上文提及的不想同步的数据库在主库添加:

binlog-ignore-db=mysql

binlog-ignore-db=information-schema

2、从库下可以忽略mysql同步:及replicate-ignore-db=mysql 从库主库必须同时使用