MySQL二进制包安装及启动问题排查

时间：2020-12-12 11:08:10

环境部署：VMware10.0+CentOS6.9(64位)+MySQL5.7.19(64位)
一、操作系统调整

# 更改时区

、先查看时区

[root@localhost ~]# date -R

Tue,  Aug  :: -

、将Asia/shanghai-上海时区写入当前时区

[root@localhost ~]# cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

cp: overwrite '/etc/localtime'? y

、再次查看时区

[root@localhost ~]# date -R

Wed,  Aug  :: +

# 更改主机名

、原主机名

[root@localhost ~]# uname -a

Linux localhost.localdomain 2.6.-.el6.x86_64 # SMP Tue Mar  :: UTC  x86_64 x86_64 x86_64 GNU/Linux

[root@localhost ~]# hostname

localhost.localdomain

、先备份原文件再修改

[root@localhost ~]# cp /etc/sysconfig/network /etc/sysconfig/network.`date +%Y%m%d.%H%M%S`

[root@localhost ~]# ll /etc/sysconfig |grep network

-rw-r--r--.  root root    Aug    network

-rw-r--r--.  root root    Aug  : network.20170830.112114

drwxr-xr-x.  root root  May    networking

drwxr-xr-x.  root root  Aug    network-scripts

[root@localhost ~]# vim /etc/sysconfig/network

NETWORKING=yes

HOSTNAME=ZST1

[root@localhost ~]#

[root@localhost ~]# cp /etc/hosts /etc/hosts.`date +%Y%m%d.%H%M%S`

[root@localhost ~]# vim /etc/hosts

#添加记录，不要修改默认的127.0.0.1跟::1的记录，其他的系统服务会使用到的

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.85.132 ZST1

192.168.85.133 ZST2

192.168.85.134 ZST3

[root@localhost ~]#

# 设定linux运行级别为3(文本模式)

、查看当前运行的模式

[root@localhost ~]# runlevel

N

、设定linux运行级别为3

[root@localhost ~]# vim /etc/inittab

id::initdefault:

、重启服务器

[root@localhost ~]# reboot

二、MySQL二进制包安装

、删除系统自带的旧rpm包(add)

[root@ZST1 ~]# rpm -qa | grep -i mysql

mysql-libs-5.1.-.el6_8.x86_64

[root@ZST1 ~]# yum -y remove mysql-libs-5.1*

[root@ZST1 ~]# whereis mysql

mysql:

、官网下载二进制包Linux Generic，下载页面可查看各版本的Change History，下载完成后验证md5

[root@ZST1 ~]# mkdir -p /tools

[root@ZST1 tools]# cd /tools

[root@ZST1 tools]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz

[root@ZST1 tools]# md5sum mysql-5.7.-linux-glibc2.-x86_64.tar.gz

dbe7e5e820377c29d8681005065e5728  mysql-5.7.-linux-glibc2.-x86_64.tar.gz

、创建帐号(运行服务的帐户都不能登录)

[root@ZST1 tools]# groupadd mysql

[root@ZST1 tools]# useradd -g mysql -d /usr/local/mysql -s /sbin/nologin -M mysql

[root@ZST1 tools]# id mysql

uid=(mysql) gid=(mysql) groups=(mysql)

[root@ZST1 tools]# 

、基本软件安装

[root@ZST1 tools]# mkdir /opt/mysql

[root@ZST1 tools]# cd /opt/mysql

[root@ZST1 mysql]# tar zxvf /tools/mysql-5.7.-linux-glibc2.-x86_64.tar.gz

[root@ZST1 mysql]# cd /usr/local/

[root@ZST1 local]# ln -s /opt/mysql/mysql-5.7.-linux-glibc2.-x86_64 mysql

[root@ZST1 local]# chown -R mysql:mysql mysql/

这里使用ln创建软链接的目的？为何不直接把/opt/mysql/mysql-5.7.-linux-glibc2.-x86_64目录下的内容拷贝到/usr/local/mysql下

、创建数据库相关的目录

配置文件：/etc/my.cnf

/data  是一个单独挂载的一个分区

datadir /data/mysql/mysql3306/data

binlog  /data/mysql/mysql3306/logs

        /data/mysql/mysql3306/tmp

[root@ZST1 local]# mkdir /data/mysql/mysql3306/{data,logs,tmp} -p

修改属主，add

[root@ZST1 local]# chown -R mysql:mysql /data/mysql/

、初始化

[root@ZST1 local]# cd /usr/local/mysql

[root@ZST1 mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize

之前版本(5.6,5.5,5.1)使用mysql_basedir/script/mysql_install_db；.7的./bin/mysql_install_db已被废弃

--initialize会生成一个随机密码(error.log)；--initialize-insecure不会生成密码

--datadir目录下不能有数据文件

[root@ZST1 mysql]# find / -name mysql_install_db

获取密码，add

[root@ZST1 mysql]# cat /data/mysql/mysql3306/data/error.log |grep password

、启动

[root@ZST1 mysql]# cp support-files/mysql.server /etc/init.d/mysql

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

[root@ZST1 mysql]# service mysql start

、连接

去掉my.cnf中--skip-grant-tables前的注释，重启数据库服务(如果能知道第6步生成的密码就不需这样折腾)

[root@ZST1 mysql]# ./bin/mysql -uroot

[root@ZST1 mysql]# ./bin/mysql -S /tmp/mysql3306.sock

进入数据库，尝试使用alter user修改密码

root@localhost [(none)]> alter user root@localhost identified by 'mysql5719';

ERROR  (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

root@localhost [(none)]> use mysql;

root@localhost [mysql]> update user set authentication_string=password('mysql5719') where User='root';

Query OK,  row affected,  warning (0.03 sec)

Rows matched:   Changed:   Warnings:

退出，注释my.cnf中--skip-grant-tables，重启数据库服务，再重新进入

root@localhost [mysql]> exit

[root@ZST1 mysql]# ./bin/mysql -uroot -p

Enter password: 键入新密码

root@localhost [(none)]> show databases;

ERROR  (HY000): You must reset your password using ALTER USER statement before executing this statement.

出现这个错误的原因是user表中的password_expired字段取值为Y，密码过期需修改

root@localhost [(none)]> alter user 'root'@'localhost' identified by 'mysql5719';

root@localhost [(none)]> exit

、配置PATH

mysql未添加环境变量前

[root@ZST1 mysql]# mysql -uroot -p

bash: mysql: command not found

[root@ZST1 mysql]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

[root@ZST1 mysql]# source /etc/profile

[root@ZST1 mysql]# echo $PATH

[root@ZST1 mysql]# mysql -uroot -p

Enter password: 键入正确密码

、关闭mysql

[root@ZST1 mysql]# /etc/init.d/mysql stop

三、遇到的问题

.5中没有修改数据库相关的目录的属主

[root@ZST1 mysql]# service mysql start

Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/mysql3306/data/mysql.pid).

查看错误日志

[root@ZST1 mysql]# tail -n  /data/mysql/mysql3306/data/error.log

--31T09::.528203Z  [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.

--31T09::.528381Z  [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

--31T09::.544406Z  [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.

--31T09::.544477Z  [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.-log) starting as process  ...

mysqld: File '/data/mysql/mysql3306/logs/mysql-bin.index' not found (Errcode:  - Permission denied)

--31T09::.548527Z  [ERROR] Aborting

--31T09::.548544Z  [Note] Binlog end

--31T09::.548838Z  [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

logs目录的权限问题(Permission denied)

[root@ZST1 mysql]# ll /data/mysql/mysql3306

total

drwxr-xr-x.  mysql mysql  Aug  : data

drwxr-xr-x.  root  root   Aug  : logs

drwxr-xr-x.  root  root   Aug  : tmp

修改目录属主信息

[root@ZST1 mysql]# cd /data/mysql/mysql3306

[root@ZST1 mysql3306]# chown -R mysql:mysql logs

[root@ZST1 mysql3306]# chown -R mysql:mysql tmp

再次启动失败

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

Starting MySQL...... ERROR! The server quit without updating PID file (/data/mysql/mysql3306/data/mysql.pid).

查看错误日志

[root@ZST1 data]# tail -n  /data/mysql/mysql3306/data/error.log

--31T09::.010606Z  [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!

--31T09::.011019Z  [Note] InnoDB: Setting file './ibdata1' size to  MB. Physically writing the file full; Please wait ...

--31T09::.011542Z  [Note] InnoDB: Progress in MB:

--31T09::.115478Z  [Note] InnoDB: File './ibdata1' size is now  MB.

--31T09::.116269Z  [Note] InnoDB: Setting log file ./ib_logfile101 size to  MB

--31T09::.116414Z  [Note] InnoDB: Progress in MB:

--31T09::.600744Z  [Note] InnoDB: Setting log file ./ib_logfile1 size to  MB

--31T09::.600967Z  [Note] InnoDB: Progress in MB:

--31T09::.127698Z  [Note] InnoDB: Setting log file ./ib_logfile2 size to  MB

--31T09::.127915Z  [Note] InnoDB: Progress in MB:

--31T09::.666114Z  [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

--31T09::.666227Z  [Warning] InnoDB: New log files created, LSN=

--31T09::.666254Z  [Note] InnoDB: Creating shared tablespace for temporary tables

--31T09::.666444Z  [Note] InnoDB: Setting file './ibtmp1' size to  MB. Physically writing the file full; Please wait ...

--31T09::.681658Z  [Note] InnoDB: File './ibtmp1' size is now  MB.

--31T09::.681940Z  [Note] InnoDB: Doublewrite buffer not found: creating new

--31T09::.851112Z  [Note] InnoDB: Doublewrite buffer created

--31T09::.902414Z  [Note] InnoDB:  redo rollback segment(s) found.  redo rollback segment(s) are active.

--31T09::.902485Z  [Note] InnoDB:  non-redo rollback segment(s) are active.

--31T09::.911638Z  [Warning] InnoDB: Creating foreign key constraint system tables.

--31T09::.956602Z  [Note] InnoDB: Foreign key constraint system tables created

--31T09::.956727Z  [Note] InnoDB: Creating tablespace and datafile system tables.

--31T09::.957381Z  [Note] InnoDB: Tablespace and datafile system tables created.

--31T09::.957432Z  [Note] InnoDB: Creating sys_virtual system tables.

--31T09::.957842Z  [Note] InnoDB: sys_virtual table created

--31T09::.023019Z  [Note] InnoDB: Waiting for purge to start

--31T09::.073741Z  [Note] InnoDB: 5.7. started; log sequence number

--31T09::.116756Z  [Note] Plugin 'FEDERATED' is disabled.

mysqld: Table 'mysql.plugin' doesn't exist

--31T09::.117295Z  [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

--31T09::.173209Z  [Note] Salting uuid generator variables, current_pid: , server_start_time: , bytes_sent: ,

--31T09::.183122Z  [Note] Generated uuid: '2bc37fda-8e32-11e7-8369-000c29c1025c', server_start_time: , bytes_sent:

--31T09::.183198Z  [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: 2bc37fda-8e32-11e7--000c29c1025c.

--31T09::.185831Z  [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

--31T09::.188080Z  [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

--31T09::.188112Z  [Note] Server hostname (bind-address): '*'; port:

--31T09::.198187Z  [Note] IPv6 is available.

--31T09::.198309Z  [Note]   - '::' resolves to '::';

--31T09::.198368Z  [Note] Server socket created on IP: '::'.

--31T09::.232943Z  [Warning] Failed to open optimizer cost constant tables

--31T09::.233342Z  [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

--31T09::.233452Z  [ERROR] Aborting

--31T09::.233515Z  [Note] Binlog end

--31T09::.235765Z  [Note] Shutting down plugin 'ngram'

--31T09::.235798Z  [Note] Shutting down plugin 'BLACKHOLE'

--31T09::.235810Z  [Note] Shutting down plugin 'ARCHIVE'

--31T09::.235818Z  [Note] Shutting down plugin 'partition'

--31T09::.235826Z  [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'

--31T09::.235834Z  [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'

--31T09::.235841Z  [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'

--31T09::.235848Z  [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'

--31T09::.235855Z  [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'

--31T09::.235863Z  [Note] Shutting down plugin 'INNODB_SYS_FIELDS'

--31T09::.235870Z  [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'

--31T09::.235877Z  [Note] Shutting down plugin 'INNODB_SYS_INDEXES'

--31T09::.235884Z  [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'

--31T09::.235891Z  [Note] Shutting down plugin 'INNODB_SYS_TABLES'

--31T09::.235898Z  [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'

--31T09::.235905Z  [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'

--31T09::.235913Z  [Note] Shutting down plugin 'INNODB_FT_CONFIG'

--31T09::.235919Z  [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'

--31T09::.235927Z  [Note] Shutting down plugin 'INNODB_FT_DELETED'

--31T09::.235934Z  [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'

--31T09::.235941Z  [Note] Shutting down plugin 'INNODB_METRICS'

--31T09::.235948Z  [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'

--31T09::.235955Z  [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'

--31T09::.235962Z  [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'

--31T09::.235969Z  [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'

--31T09::.235985Z  [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'

--31T09::.235994Z  [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'

--31T09::.236001Z  [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'

--31T09::.236008Z  [Note] Shutting down plugin 'INNODB_CMPMEM'

--31T09::.236016Z  [Note] Shutting down plugin 'INNODB_CMP_RESET'

--31T09::.236023Z  [Note] Shutting down plugin 'INNODB_CMP'

--31T09::.236030Z  [Note] Shutting down plugin 'INNODB_LOCK_WAITS'

--31T09::.236038Z  [Note] Shutting down plugin 'INNODB_LOCKS'

--31T09::.236045Z  [Note] Shutting down plugin 'INNODB_TRX'

--31T09::.236052Z  [Note] Shutting down plugin 'InnoDB'

--31T09::.236349Z  [Note] InnoDB: FTS optimize thread exiting.

--31T09::.236499Z  [Note] InnoDB: Starting shutdown...

--31T09::.337034Z  [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3306/data/ib_buffer_pool

--31T09::.337528Z  [Note] InnoDB: Buffer pool(s) dump completed at  ::

--31T09::.862954Z  [Note] InnoDB: Shutdown completed; log sequence number

--31T09::.863526Z  [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

--31T09::.863560Z  [Note] Shutting down plugin 'MEMORY'

--31T09::.863576Z  [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'

--31T09::.863762Z  [Note] Shutting down plugin 'MRG_MYISAM'

--31T09::.863777Z  [Note] Shutting down plugin 'MyISAM'

--31T09::.865027Z  [Note] Shutting down plugin 'CSV'

--31T09::.865062Z  [Note] Shutting down plugin 'sha256_password'

--31T09::.865075Z  [Note] Shutting down plugin 'mysql_native_password'

--31T09::.865415Z  [Note] Shutting down plugin 'binlog'

--31T09::.868573Z  [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

重新初始化

[root@ZST1 mysql]# pwd

/usr/local/mysql

[root@ZST1 mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize

--31T09::.865304Z  [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.

--31T09::.865492Z  [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

--31T09::.865561Z  [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap.

--31T09::.865593Z  [Note] ./bin/mysqld (mysqld 5.7.-log) starting as process  ...

--31T09::.868650Z  [ERROR] --initialize specified but the data directory has files in it. Aborting.

--31T09::.868696Z  [ERROR] Aborting

--31T09::.868723Z  [Note] Binlog end

--31T09::.868950Z  [Note] ./bin/mysqld: Shutdown complete

数据目录有文件存在(data directory has files in it)，.6也提到初始化时--datadir目录下不能有数据文件

删除/data/mysql/mysql3306/{data,logs,tmp}目录下的所有文件(夹)，再重新初始化

对于Errcode可以使用perror命令查看错误描述

[root@ZST1 ~]# /usr/local/mysql/bin/perror

OS error code  :  Permission denied

四、my.cnf

#my.cnf

[client]

port            =

socket          = /tmp/mysql3306.sock

[mysql]

prompt="\\u@\\h,\\p [\\d]>\\_"

#pager="less -i -n -S"

#tee=/opt/mysql/query.log

no-auto-rehash

[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

port = 

socket = /tmp/mysql3306.sock

event_scheduler = 

tmpdir = /data/mysql/mysql3306/tmp

#timeout

interactive_timeout =

wait_timeout = 

#character set

character-set-server = utf8

open_files_limit =

max_connections =

max_connect_errors =

lower_case_table_names =

#file

#@secure-file-priv=/tmp

#symi replication

#rpl_semi_sync_master_enabled=

#rpl_semi_sync_master_timeout= #  second

#rpl_semi_sync_slave_enabled=

#logs

log-output=file

slow_query_log =

slow_query_log_file = slow.log

log-error = error.log

log_warnings =

pid-file = mysql.pid

long_query_time =

#log-slow-admin-statements =

#log-queries-not-using-indexes =

log-slow-slave-statements = 

#binlog

#binlog_format = STATEMENT

binlog_format = row

server-id =

log-bin = /data/mysql/mysql3306/logs/mysql-bin

max_binlog_size = 256M

sync_binlog =

expire_logs_days =

#procedure

log_bin_trust_function_creators=

#file

secure_file_priv="/tmp"

#

gtid-mode = on

enforce-gtid-consistency=

#relay log

skip_slave_start =

max_relay_log_size = 128M

relay_log_purge =

relay_log_recovery =

relay-log=relay-bin

relay-log-index=relay-bin.index

log_slave_updates

#slave-skip-errors=,,

#skip-grant-tables

#buffers & cache

table_open_cache =

table_definition_cache =

table_open_cache =

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size =

query_cache_size =

query_cache_type =

query_cache_limit = 256K

query_cache_min_res_unit =

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances =

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit =

innodb_log_buffer_size = 8M

innodb_log_file_size = 100M

innodb_log_files_in_group =

innodb_max_dirty_pages_pct =

innodb_file_per_table =

innodb_rollback_on_timeout

innodb_io_capacity =

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

问题：能否使用二进制包在同一机器上搭建多实例？多实例主要是my.cnf怎么区分？

补一、宿主机用Navicat连接虚拟机上的MySQL实例

、宿主机用Navicat连接虚拟机上的MySQL实例

 - Cannot connect to MySQL server on '192.168.85.132'()

、宿主机ping、telnet虚拟机

C:\Users\Administrator>ping 192.168.85.132

正在 Ping 192.168.85.132 具有  字节的数据:

来自 192.168.85.132 的回复: 字节= 时间<1ms TTL=

来自 192.168.85.132 的回复: 字节= 时间<1ms TTL=

来自 192.168.85.132 的回复: 字节= 时间<1ms TTL=

来自 192.168.85.132 的回复: 字节= 时间<1ms TTL=

192.168.85.132 的 Ping 统计信息:

    数据包: 已发送 = ，已接收 = ，丢失 =  (% 丢失)，

往返行程的估计时间(以毫秒为单位):

    最短 = 0ms，最长 = 0ms，平均 = 0ms

C:\Users\Administrator>telnet 192.168.85.132

正在连接192.168.85....无法打开到主机的连接。 在端口 : 连接失败

、虚拟机检查防火墙

[root@ZST1 ~]# service iptables status

Table: filter

Chain INPUT (policy ACCEPT)

num  target     prot opt source               destination

    ACCEPT     all  --  0.0.0.0/            0.0.0.0/           state RELATED,ESTABLISHED

    ACCEPT     icmp --  0.0.0.0/            0.0.0.0/

    ACCEPT     all  --  0.0.0.0/            0.0.0.0/

    ACCEPT     tcp  --  0.0.0.0/            0.0.0.0/           state NEW tcp dpt:

    REJECT     all  --  0.0.0.0/            0.0.0.0/           reject-with icmp-host-prohibited 

Chain FORWARD (policy ACCEPT)

num  target     prot opt source               destination

    REJECT     all  --  0.0.0.0/            0.0.0.0/           reject-with icmp-host-prohibited 

Chain OUTPUT (policy ACCEPT)

num  target     prot opt source               destination         

暂时关闭防火墙，后期添加入站规则

[root@ZST1 ~]# service iptables stop

[root@ZST1 ~]# cp /etc/sysconfig/iptables /etc/sysconfig/iptables.`date +%Y%m%d.%H%M%S`

-I作为第一条规则插入，-A作为最后一条规则插入

[root@ZST1 ~]# iptables -I INPUT -p tcp --dport  -j ACCEPT

[root@ZST1 ~]# service iptables save

[root@ZST1 ~]# service iptables restart

、宿主机Navicat连接虚拟机上的MySQL实例

-Access denied for user 'root'@'192.168.85.1'(using password:Yes)

用户表中没有授权(只有root@localhost)

、mysql创建新用户

[root@ZST1 ~]# mysql -uroot -p

root@localhost,mysql3306.sock [(none)]> grant all privileges on *.* to mydba@"192.168.85.%" Identified by "mysql5719";

之后就可以从宿主机用Navicat连接虚拟机上的MySQL实例

可使用rename user修改User和Host信息

root@localhost,mysql3306.sock [(none)]> rename user 'mydba'@'192.168.85.%' TO 'mydba'@'localhost';

root@localhost,mysql3306.sock [(none)]> show grants for 'mydba'@'192.168.85.%';

rename user后不需要flush privileges，会直接更新内存中的权限信息；

Navicat打开的命令列界面，不会受my.cnf中的prompt影响

使用rename user修改User和Host信息，rename user后不需要flush privileges，会直接更新内存中的权限信息；
补二、二进制包/源码安装方式的MySQL卸载

、检查MySQL服务并关闭服务进程

[root@ZST1 ~]# ps -ef | grep mysql

root              : pts/    :: /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/mysql3306/data --pid-file=/data/mysql/mysql3306/data/mysql.pid

mysql          : pts/    :: /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=error.log --open-files-limit= --pid-file=/data/mysql/mysql3306/data/mysql.pid --socket=/tmp/mysql3306.sock --port=

root           : pts/    :: mysql -uroot -p

root           : pts/    :: grep mysql

[root@ZST1 ~]# service mysql status

 SUCCESS! MySQL running ()

[root@ZST1 ~]# service mysql stop

Shutting down MySQL.. SUCCESS!

[root@ZST1 ~]# service mysql status

 ERROR! MySQL is not running

[root@ZST1 ~]# 

、查找MySQL的安装目录并彻底删除

[root@ZST1 ~]# whereis mysql

mysql: /usr/lib64/mysql /usr/local/mysql /usr/share/mysql

[root@ZST1 ~]# find / -name mysql

/usr/local/mysql

/usr/lib64/mysql

/usr/share/mysql

/var/spool/mail/mysql

/opt/mysql

/opt/mysql/mysql-5.7.-linux-glibc2.-x86_64/bin/mysql

/opt/mysql/mysql-5.7.-linux-glibc2.-x86_64/include/mysql

/etc/rc.d/init.d/mysql

/data/mysql

/data/mysql/mysql3306/data/mysql

[root@ZST1 ~]# 

和原文稍有不同，ZST1之前存在mysql-libs

[root@ZST1 ~]# rpm -qa|grep -i mysql

mysql-libs-5.1.-.el6_8.x86_64

[root@ZST1 ~]# yum -y remove mysql-libs-5.1.-.el6_8.x86_64

[root@ZST1 ~]# ll /etc |grep my.cnf

-rw-r--r--.   root root    Sep   : my.cnf.rpmsave

[root@ZST1 ~]# whereis mysql

mysql: /usr/local/mysql

[root@ZST1 ~]# find / -name mysql

/usr/local/mysql

/var/spool/mail/mysql

/opt/mysql

/opt/mysql/mysql-5.7.-linux-glibc2.-x86_64/bin/mysql

/opt/mysql/mysql-5.7.-linux-glibc2.-x86_64/include/mysql

/etc/rc.d/init.d/mysql

/data/mysql

/data/mysql/mysql3306/data/mysql

[root@ZST1 ~]# rm -rf /var/spool/mail/mysql

[root@ZST1 ~]# rm -rf /etc/rc.d/init.d/mysql

[root@ZST1 ~]# rm -rf /data/mysql/mysql3306

、删除一些配置文件

配置文件一般有/etc/my.cnf 或/etc/init.d/mysql.server，视具体安装配置情况而定。

、删除MySQL用户以及用户组

[root@ZST1 ~]# id mysql

uid=(mysql) gid=(mysql) groups=(mysql)

[root@ZST1 ~]# userdel mysql

$PATH里面还有mysql信息，第3、4步暂时不处理

在前面删除的基础上重新使用二进制包安装MySQL

、创建数据库相关的目录

[root@ZST1 ~]# mkdir /data/mysql/mysql3306/{data,logs,tmp} -p

[root@ZST1 ~]# chown -R mysql:mysql /data/mysql/

、初始化

[root@ZST1 ~]# cd /usr/local/mysql

[root@ZST1 mysql]# mv /etc/my.cnf.rpmsave /etc/my.cnf

[root@ZST1 mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize

找不到那个所谓的~/.mysql_secret文件

[root@ZST1 mysql]# more ~/.mysql_secret

/root/.mysql_secret: No such file or directory

初始密码从error.log中查找

[root@ZST1 mysql]# cat /data/mysql/mysql3306/data/error.log |grep password

--05T08::.396068Z  [Note] A temporary password is generated for root@localhost: FpN=_tXkg2zW

相关文章