数据仓库006 - MySQL 5.6.x - Linux最佳生产环境离线部署
一、离线安装包 文件准备
这里以mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz为例，记一次MySQL 5.6.x 的生产环境离线部署过程。使用SecureCRT连接生产部署的机器，这里注意使用root用户进行操作。
mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz文件，已放到网盘（为什么不放到仓库的原因：****,习惯了 ），
链接：https://pan.baidu.com/s/1aOC4jUiVa5KcLS7dQwyCbA 提取码：kvuo
1、检查是否安装过mysqld
[root@slave054 local]# ps -ef|grep mysqld root 2428 2282 0 20:25 pts/0 00:00:00 grep --color=auto mysqld
[root@slave054 local]# rpm -qa |grep -i mysql
[root@slave054 local]# ps -ef|grep mysqld root 2432 2282 0 20:25 pts/0 00:00:00 grep --color=auto mysqld
2、切换到/usr/local目录下
[root@slave054 ~]# cd /usr/local [root@slave054 local]# pwd /usr/local [root@slave054 local]#
3、使用rz 上传mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz压缩文件
[root@slave054 local]# ll total 0 drwxr-xr-x. 2 root root 6 Apr 11 2018 bin drwxr-xr-x. 2 root root 6 Apr 11 2018 etc drwxr-xr-x. 2 root root 6 Apr 11 2018 games drwxr-xr-x. 2 root root 6 Apr 11 2018 include drwxr-xr-x. 2 root root 6 Apr 11 2018 lib drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64 drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin drwxr-xr-x. 5 root root 49 Nov 24 00:00 share drwxr-xr-x. 2 root root 6 Apr 11 2018 src [root@slave054 local]# rz rz waiting to receive. zmodem trl+C ȡ 100% 304464 KB 2495 KB/s 00:02:02 0 Errorssc2.5-x86_64.tar.gz... [root@slave054 local]#
[root@slave054 local]# ll total 304468 drwxr-xr-x. 2 root root 6 Apr 11 2018 bin drwxr-xr-x. 2 root root 6 Apr 11 2018 etc drwxr-xr-x. 2 root root 6 Apr 11 2018 games drwxr-xr-x. 2 root root 6 Apr 11 2018 include drwxr-xr-x. 2 root root 6 Apr 11 2018 lib drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64 drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec -rw-r--r--. 1 root root 311771412 Nov 23 19:48 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin drwxr-xr-x. 5 root root 49 Nov 24 00:00 share drwxr-xr-x. 2 root root 6 Apr 11 2018 src [root@slave054 local]#
二、解压文件并建立软连接
1、解压mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[root@slave054 local]# tar xzvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
2、建立软连接
[root@slave054 local]# ln -s mysql-5.6.23-linux-glibc2.5-x86_64 mysql
[root@slave054 local]# ll total 304468 drwxr-xr-x. 2 root root 6 Apr 11 2018 bin drwxr-xr-x. 2 root root 6 Apr 11 2018 etc drwxr-xr-x. 2 root root 6 Apr 11 2018 games drwxr-xr-x. 2 root root 6 Apr 11 2018 include drwxr-xr-x. 2 root root 6 Apr 11 2018 lib drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64 drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec lrwxrwxrwx. 1 root root 34 Nov 25 20:32 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64 drwxr-xr-x. 13 root root 213 Nov 25 20:31 mysql-5.6.23-linux-glibc2.5-x86_64 -rw-r--r--. 1 root root 311771412 Nov 23 19:48 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin drwxr-xr-x. 5 root root 49 Nov 24 00:00 share drwxr-xr-x. 2 root root 6 Apr 11 2018 src [root@slave054 local]#
三、用户和用户组
1、添加dba用户组
[root@slave054 local]# groupadd -g 101 dba
2、新建mysqladmin用户
[root@slave054 local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin useradd: warning: the home directory already exists. Not copying any file from skel directory into it. [root@slave054 local]# id mysqladmin uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root) [root@slave054 local]# passwd mysqladmin Changing password for user mysqladmin. New password: BAD PASSWORD: The password is shorter than 8 characters Retype new password: passwd: all authentication tokens updated successfully. [root@slave054 local]#
如果mysqladmin 已经存在，则执行如下命令：
usermod -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
3、复制环境变量配置文件至mysqladmin用户的home目录
[root@slave054 local]# cp /etc/skel/.* /usr/local/mysql cp: omitting directory ‘/etc/skel/.’ cp: omitting directory ‘/etc/skel/..’ cp: omitting directory ‘/etc/skel/.mozilla’ [root@slave054 local]#
4、my.cnf文件配置
[root@slave054 local]# cd /etc/
[root@slave054 etc]# vi my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [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 ~ ~ ~ ~ "my.cnf" 19L, 570C
将以下的内容，替换掉原来默认的my.cnf默认配置内容，注意vim要进入-- INSERT --模式才能开始粘贴，否则粘贴的内容会不完全：
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [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 [client] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 socket = /usr/local/mysql/data/mysql.sock skip-external-locking key_buffer_size = 256M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M query_cache_size= 32M max_allowed_packet = 16M myisam_sort_buffer_size=128M tmp_table_size=32M table_open_cache = 512 thread_cache_size = 8 wait_timeout = 86400 interactive_timeout = 86400 max_connections = 600 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 32 #isolation level and default engine default-storage-engine = INNODB transaction-isolation = READ-COMMITTED server-id = 1 basedir = /usr/local/mysql datadir = /usr/local/mysql/data pid-file = /usr/local/mysql/data/hostname.pid #open performance schema log-warnings sysdate-is-now binlog_format = MIXED log_bin_trust_function_creators=1 log-error = /usr/local/mysql/data/hostname.err log-bin=/usr/local/mysql/arch/mysql-bin #other logs #general_log =1 #general_log_file = /usr/local/mysql/data/general_log.err #slow_query_log=1 #slow_query_log_file=/usr/local/mysql/data/slow_log.err #for replication slave #log-slave-updates #sync_binlog = 1 #for innodb options innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:500M:autoextend innodb_log_group_home_dir = /usr/local/mysql/arch innodb_log_files_in_group = 2 innodb_log_file_size = 200M innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 50M innodb_log_buffer_size = 16M innodb_lock_wait_timeout = 100 #innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 1 innodb_locks_unsafe_for_binlog=1 #innodb io features: add for mysql5.5.8 performance_schema innodb_read_io_threads=4 innodb-write-io-threads=4 innodb-io-capacity=200 #purge threads change default(0) to 1 for purge innodb_purge_threads=1 innodb_use_native_aio=on #case-sensitive file names and separate tablespace innodb_file_per_table = 1 lower_case_table_names=1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [mysqlhotcopy] interactive-timeout [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M
如果需要进行mysql调优，只需要调整*_buffer_size的参数即可，切记根据实际需求修改。
注意：
defualt start: /etc/my.cnf->/etc/mysql/my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> --defaults-extra-file->~/my.cnf
5、权限与文件所有者
[root@slave054 etc]# cd - /usr/local [root@slave054 local]# chown mysqladmin:dba /etc/my.cnf [root@slave054 local]# chmod 640 /etc/my.cnf [root@slave054 local]# ll /etc/my.cnf -rw-r-----. 1 mysqladmin dba 2218 Nov 25 20:48 /etc/my.cnf [root@slave054 local]# ll |grep mysql lrwxrwxrwx. 1 root root 34 Nov 25 20:32 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64 drwxr-xr-x. 13 root root 269 Nov 25 20:42 mysql-5.6.23-linux-glibc2.5-x86_64 -rw-r--r--. 1 root root 311771412 Nov 23 19:48 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz [root@slave054 local]# chown -R mysqladmin:dba /usr/local/mysql [root@slave054 local]# ll |grep mysql lrwxrwxrwx. 1 mysqladmin dba 34 Nov 25 20:32 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64 drwxr-xr-x. 13 root root 269 Nov 25 20:42 mysql-5.6.23-linux-glibc2.5-x86_64 -rw-r--r--. 1 root root 311771412 Nov 23 19:48 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz [root@slave054 local]# chown -R mysqladmin:dba /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64 [root@slave054 local]# ll |grep mysql lrwxrwxrwx. 1 mysqladmin dba 34 Nov 25 20:32 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64 drwxr-xr-x. 13 mysqladmin dba 269 Nov 25 20:42 mysql-5.6.23-linux-glibc2.5-x86_64 -rw-r--r--. 1 root root 311771412 Nov 23 19:48 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz [root@slave054 local]# chmod -R 755 /usr/local/mysql [root@slave054 local]# chmod -R 755 /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64 [root@slave054 local]# ll |grep mysql lrwxrwxrwx. 1 mysqladmin dba 34 Nov 25 20:32 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64 drwxr-xr-x. 13 mysqladmin dba 269 Nov 25 20:42 mysql-5.6.23-linux-glibc2.5-x86_64 -rw-r--r--. 1 root root 311771412 Nov 23 19:48 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz [root@slave054 local]#
四、mysqladmin用户配置mysql
1、登录mysqladmin用户
[root@slave054 local]# su - mysqladmin [mysqladmin@slave054 ~]$ pwd /usr/local/mysql [mysqladmin@slave054 ~]$ ll total 148 drwxr-xr-x. 2 mysqladmin dba 4096 Nov 25 20:31 bin -rwxr-xr-x. 1 mysqladmin dba 17987 Jan 19 2015 COPYING drwxr-xr-x. 3 mysqladmin dba 18 Nov 25 20:30 data drwxr-xr-x. 2 mysqladmin dba 55 Nov 25 20:31 docs drwxr-xr-x. 3 mysqladmin dba 4096 Nov 25 20:31 include -rwxr-xr-x. 1 mysqladmin dba 102315 Jan 19 2015 INSTALL-BINARY drwxr-xr-x. 3 mysqladmin dba 4096 Nov 25 20:31 lib drwxr-xr-x. 4 mysqladmin dba 30 Nov 25 20:31 man drwxr-xr-x. 10 mysqladmin dba 4096 Nov 25 20:31 mysql-test -rwxr-xr-x. 1 mysqladmin dba 2496 Jan 19 2015 README drwxr-xr-x. 2 mysqladmin dba 30 Nov 25 20:31 scripts drwxr-xr-x. 28 mysqladmin dba 4096 Nov 25 20:31 share drwxr-xr-x. 4 mysqladmin dba 4096 Nov 25 20:31 sql-bench drwxr-xr-x. 2 mysqladmin dba 136 Nov 25 20:31 support-files [mysqladmin@slave054 ~]$
2、新建backup目录
[mysqladmin@slave054 ~]$ mkdir arch backup
3、使用scripts安装mysql_install_db
[mysqladmin@slave054 ~]$ scripts/mysql_install_db Installing MySQL system tables...2019-11-25 21:01:32 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-11-25 21:01:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). OK Filling help tables...2019-11-25 21:01:38 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-11-25 21:01:38 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h slave054 password 'new-password' Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; /usr/local/mysql/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as ./my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [mysqladmin@slave054 ~]$
注意：如果第3步骤中，报了一个
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory #缺少libaio.so 包 错误，为了修正这个错误，就需要 先执行 如下命令，之后再切换回mysqladmin用户，继续执行第3步骤中的内容：
* 4-1、查看Linux version
[mysqladmin@slave054 ~]$ cat /proc/version
Linux version 3.10.0-1062.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1 SMP Wed Aug 7 18:08:02 UTC 2019
[mysqladmin@slave054 ~]$
* 4-2、gcc （建议使用root用户进行操作）
[mysqladmin@slave054 ~]$ rpm -qa |grep gcc gcc-c++-4.8.5-39.el7.x86_64 gcc-4.8.5-39.el7.x86_64 libgcc-4.8.5-39.el7.x86_64 gcc-gfortran-4.8.5-39.el7.x86_64 [mysqladmin@slave054 ~]$
* 4-3、切换root用户，安装libaio 包
[mysqladmin@slave054 ~]$ su - root Password: Last login: Mon Nov 25 20:15:13 CST 2019 on pts/0 [root@slave054 ~]# yum -y install libaio Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirror.lzu.edu.cn * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com base | 3.6 kB 00:00 extras | 2.9 kB 00:00 updates | 2.9 kB 00:00 Package libaio-0.3.109-13.el7.x86_64 already installed and latest version Nothing to do [root@slave054 ~]#
Loaded plugins: fastestmirror Determining fastest mirrors base-tn54 | 1.1 kB 00:00 tn-appv1 | 951 B 00:00 updates-tn54 | 951 B 00:00 Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package libaio.i386 0:0.3.106-3.2 set to be updated ---> Package libaio.x86_64 0:0.3.106-3.2 set to be updated --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================== Package Arch Version Repository Size ============================================================================================================================== Installing: libaio i386 0.3.106-3.2 base-tn54 19 k libaio x86_64 0.3.106-3.2 base-tn54 19 k Transaction Summary ============================================================================================================================== Install 2 Package(s) Update 0 Package(s) Remove 0 Package(s) Total download size: 38 k Downloading Packages: (1/2): libaio-0.3.106-3.2.i386.rpm | 19 kB 00:00 (2/2): libaio-0.3.106-3.2.x86_64.rpm | 19 kB 00:00 ------------------------------------------------------------------------------------------------------------------------------ Total 452 kB/s | 38 kB 00:00 Running rpm_check_debug Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Installing : libaio 1/2 Installing : libaio 2/2 Installed: libaio.i386 0:0.3.106-3.2 libaio.x86_64 0:0.3.106-3.2 Complete!
* 4-4、重新切换mysqladmin用户
[root@slave054 ~]# su - mysqladmin Last login: Mon Nov 25 20:59:20 CST 2019 on pts/0 [mysqladmin@slave054 ~]$ pwd /usr/local/mysql [mysqladmin@slave054 ~]$
五、初始化db
[mysqladmin@slave054 ~]$ scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[mysqladmin@slave054 ~]$ scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data Installing MySQL system tables...2019-11-25 21:18:35 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-11-25 21:18:35 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). OK Filling help tables...2019-11-25 21:18:37 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-11-25 21:18:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h slave054 password 'new-password' Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; /usr/local/mysql/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file /usr/local/mysql/my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as /usr/local/mysql/my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [mysqladmin@slave054 ~]$
六、配置mysql 自启服务
1、切换root用户
[mysqladmin@slave054 ~]$ su - root Password: Last login: Mon Nov 25 21:12:35 CST 2019 on pts/0 [root@slave054 ~]#
2、将服务文件拷贝到init.d下，并重命名为mysql
[root@slave054 ~]# cd /usr/local/mysql [root@slave054 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql [root@slave054 mysql]#
3、赋予可执行权限
[root@slave054 mysql]# chmod +x /etc/rc.d/init.d/mysql
[root@slave054 mysql]#
4、添加服务
这里为了防止之前安装过mysql服务，而导致此处不成功，这里先删除服务之后再安装服务。
[root@slave054 mysql]# chkconfig --del mysql [root@slave054 mysql]# chkconfig --add mysql [root@slave054 mysql]# chkconfig --level 345 mysql on [root@slave054 mysql]#
5、写入便捷操作
[root@slave054 mysql]# echo 'su - mysqladmin -c "/etc/init.d/mysql start --federated"' >>/etc/rc.local [root@slave054 mysql]# tail /etc/rc.local # to run scripts during boot instead of using this file. # # In contrast to previous versions due to parallel execution during boot # this script will NOT be run after all other services. # # Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure # that this script will be executed during boot. touch /var/lock/subsys/local su - mysqladmin -c "/etc/init.d/mysql start --federated" [root@slave054 mysql]#
七、启动mysql 服务
1、切换mysqladmin用户
[root@slave054 mysql]# su - mysqladmin Last login: Mon Nov 25 21:17:15 CST 2019 on pts/0 [mysqladmin@slave054 ~]$ pwd /usr/local/mysql [mysqladmin@slave054 ~]$
建议不执行
[mysqladmin@slave054 ~]$ ll total 156 drwxr-xr-x. 2 mysqladmin dba 163 Nov 25 21:18 arch drwxr-xr-x. 2 mysqladmin dba 6 Nov 25 21:01 backup drwxr-xr-x. 2 mysqladmin dba 4096 Nov 25 20:31 bin -rwxr-xr-x. 1 mysqladmin dba 17987 Jan 19 2015 COPYING drwxr-xr-x. 5 mysqladmin dba 92 Nov 25 21:18 data drwxr-xr-x. 2 mysqladmin dba 55 Nov 25 20:31 docs drwxr-xr-x. 3 mysqladmin dba 4096 Nov 25 20:31 include -rwxr-xr-x. 1 mysqladmin dba 102315 Jan 19 2015 INSTALL-BINARY drwxr-xr-x. 3 mysqladmin dba 4096 Nov 25 20:31 lib drwxr-xr-x. 4 mysqladmin dba 30 Nov 25 20:31 man -rw-r--r--. 1 mysqladmin dba 943 Nov 25 21:01 my.cnf -rw-r--r--. 1 mysqladmin dba 943 Nov 25 21:18 my-new.cnf drwxr-xr-x. 10 mysqladmin dba 4096 Nov 25 20:31 mysql-test -rwxr-xr-x. 1 mysqladmin dba 2496 Jan 19 2015 README drwxr-xr-x. 2 mysqladmin dba 30 Nov 25 20:31 scripts drwxr-xr-x. 28 mysqladmin dba 4096 Nov 25 20:31 share drwxr-xr-x. 4 mysqladmin dba 4096 Nov 25 20:31 sql-bench drwxr-xr-x. 2 mysqladmin dba 136 Nov 25 20:31 support-files [mysqladmin@slave054 ~]$ mv my.cnf my.cnf.bak [mysqladmin@slave054 ~]$ ll total 156 drwxr-xr-x. 2 mysqladmin dba 163 Nov 25 21:18 arch drwxr-xr-x. 2 mysqladmin dba 6 Nov 25 21:01 backup drwxr-xr-x. 2 mysqladmin dba 4096 Nov 25 20:31 bin -rwxr-xr-x. 1 mysqladmin dba 17987 Jan 19 2015 COPYING drwxr-xr-x. 5 mysqladmin dba 92 Nov 25 21:18 data drwxr-xr-x. 2 mysqladmin dba 55 Nov 25 20:31 docs drwxr-xr-x. 3 mysqladmin dba 4096 Nov 25 20:31 include -rwxr-xr-x. 1 mysqladmin dba 102315 Jan 19 2015 INSTALL-BINARY drwxr-xr-x. 3 mysqladmin dba 4096 Nov 25 20:31 lib drwxr-xr-x. 4 mysqladmin dba 30 Nov 25 20:31 man -rw-r--r--. 1 mysqladmin dba 943 Nov 25 21:01 my.cnf.bak -rw-r--r--. 1 mysqladmin dba 943 Nov 25 21:18 my-new.cnf drwxr-xr-x. 10 mysqladmin dba 4096 Nov 25 20:31 mysql-test -rwxr-xr-x. 1 mysqladmin dba 2496 Jan 19 2015 README drwxr-xr-x. 2 mysqladmin dba 30 Nov 25 20:31 scripts drwxr-xr-x. 28 mysqladmin dba 4096 Nov 25 20:31 share drwxr-xr-x. 4 mysqladmin dba 4096 Nov 25 20:31 sql-bench drwxr-xr-x. 2 mysqladmin dba 136 Nov 25 20:31 support-files [mysqladmin@slave054 ~]$ mysqld_safe & [1] 56460 [mysqladmin@slave054 ~]$ 191125 21:39:11 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'. 191125 21:39:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data ^C [mysqladmin@slave054 ~]$ ps -ef|grep mysqld mysqlad+ 56460 56369 0 21:39 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe mysqlad+ 57103 56460 1 21:39 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/hostname.err --pid-file=/usr/local/mysql/data/hostname.pid --socket=/usr/local/mysql/data/mysql.sock --port=3306 mysqlad+ 57126 56369 0 21:39 pts/0 00:00:00 grep --color=auto mysqld [mysqladmin@slave054 ~]$ netstat -tulnp | grep mysql (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp6 0 0 :::3306 :::* LISTEN 57103/mysqld [mysqladmin@slave054 ~]$ mv my.cnf.bak my.cnf
2、启动mysql 服务
[mysqladmin@slave054 ~]$ service mysql start Starting MySQL SUCCESS! [mysqladmin@slave054 ~]$ service mysql status SUCCESS! MySQL running (57103) [mysqladmin@slave054 ~]$
3、登录mysql
[mysqladmin@slave054 ~]$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.23-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) mysql>
*八、数据库部分被隐匿-解决方法
这里Database只有 information_schema 和 test，说明mysql数据库的user表里，存在用户名为空的账户即匿名账户，导致登录的时候是虽然用的是root，但实际是匿名登录的，通过错误提示里的''@'localhost'可以看出来
1、先停止服务，并mysqld_safe执行
[mysqladmin@slave054 ~]$ service mysql stop Shutting down MySQL.. SUCCESS! [mysqladmin@slave054 ~]$ service mysql status ERROR! MySQL is not running [mysqladmin@slave054 ~]$ mysqld_safe --skip-grant-table 191125 21:57:34 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'. 191125 21:57:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2、新开克隆窗口，程序登录恢复正常：
[linux@slave054 ~]$ su - mysqladmin Password: Last login: Mon Nov 25 21:54:59 CST 2019 on pts/0 [mysqladmin@slave054 ~]$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.23-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
3、删除匿名登录
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 28 rows in set (0.00 sec) mysql> delete from user where USER=''; Query OK, 2 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye [mysqladmin@slave054 ~]$
九、修改登录密码
[mysqladmin@slave054 ~]$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.23-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> use mysql Database changed mysql> update user set password=password('123456') where user='root'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select host,user,password from user; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | slave054 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> delete from user where user=''; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
在一定程度上说，mysql 5.7版本之前的初次默认密码为空，mysql 5.7以后默认密码为随机数。
十、配置无主机登录
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 28 rows in set (0.00 sec) mysql> select host,user from user; +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | ::1 | root | | localhost | root | | slave054 | root | +-----------+------+ 4 rows in set (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> select host,user from user; +-----------+------+ | host | user | +-----------+------+ | % | root | | 127.0.0.1 | root | | ::1 | root | | localhost | root | | slave054 | root | +-----------+------+ 5 rows in set (0.00 sec) mysql> delete from user where host !='%'; Query OK, 4 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye [mysqladmin@slave054 ~]$
此时不能再使用mysql -uroot进行登录 ，而一定要使用 mysql -uroot -p进行登录，切记 -p后面不能写明密码，防止密码泄露！如：
[mysqladmin@slave054 ~]$ mysql -uroot ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [mysqladmin@slave054 ~]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.23-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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>
十一、写入配置到用户个人环境变量文件
·这里不写/etc/profile的原因：mysqladmin用户专职进行mysql的管理，独自集中管理，拒绝权利外配，所以就连全局下的root不分配。
·这里不写.bash_profile的原因：在使用SecureCRT连接时，.bash_profile的部分配置参数不被加载
所以经过分析，写入 .bashrc文件，将下列内容追加到 .bashrc文件中，切记先进入-- INSERT -- 模式再粘贴，否则容易发生粘贴不完全的现象：
# User specific environment and startup programs MYSQL_BASE=/usr/local/mysql export MYSQL_BASE PATH=$PATH:${MYSQL_BASE}/bin:/usr/kerberos/bin:/opt/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/local/mysql export PATH export PATH unset USERNAME #stty erase set umask to 022 umask 022 PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1 alias l="ls -ltr" alias la="ls -la" alias d="df -lk" #alias rm="rm -i" alias bg="ps -aef|grep mysql" alias base="cd /usr/local/mysql" alias bin="cd //usr/local/mysql/bin" alias dba="cd /usr/local/mysql/dba" alias sh="cd /usr/local/mysql/dba/sh" alias sql="cd /usr/local/mysql/dba/sql" alias config="cd /usr/local/mysql/dba/config" alias dbalog="cd /usr/local/mysql/dba/log" alias arch="cd /usr/local/mysql/arch" alias data="cd /usr/local/mysql/data"
这样重新登录mysqladmin用户，会改变mysqladmin的样式，方便生产过程：
[mysqladmin@slave054 ~]$ exit logout [linux@slave054 ~]$ su - mysqladmin Password: Last login: Mon Nov 25 22:08:25 CST 2019 on pts/1 slave054:mysqladmin:/usr/local/mysql:>
注意：
如果发生如下错误，对应修改即可：
Error1: File '/usr/local/mysql/arch/mysql-bin.index' not found (Errcode: 13) test2.localdomain:mysqladmin:/usr/local/mysql/arch:>chmod 755 * test2.localdomain:mysqladmin:/usr/local/mysql/arch:>chown –R mysqladmin:dba *
十二、远程连接问题
见图：
先登录root用户，关闭防火墙：
slave054:mysqladmin:/usr/local/mysql:>su - root Password: Last login: Mon Nov 25 21:54:10 CST 2019 on pts/0 [root@slave054 ~]# systemctl start firewalld [root@slave054 ~]# systemctl stop firewalld [root@slave054 ~]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: inactive (dead) since Mon 2019-11-25 22:49:16 CST; 8s ago Docs: man:firewalld(1) Process: 861 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS) Main PID: 861 (code=exited, status=0/SUCCESS) Nov 25 20:09:55 slave054 systemd[1]: Starting firewalld - dynamic firewall ..... Nov 25 20:09:56 slave054 systemd[1]: Started firewalld - dynamic firewall d...n. Nov 25 22:49:15 slave054 systemd[1]: Stopping firewalld - dynamic firewall ..... Nov 25 22:49:16 slave054 systemd[1]: Stopped firewalld - dynamic firewall d...n. Hint: Some lines were ellipsized, use -l to show in full. [root@slave054 ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@slave054 ~]# systemctl enable firewalld Created symlink from /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service to /usr/lib/systemd/system/firewalld.service. Created symlink from /etc/systemd/system/multi-user.target.wants/firewalld.service to /usr/lib/systemd/system/firewalld.service. [root@slave054 ~]#
再次测试连接成功，见图：
