数据仓库006 - MySQL 5.6.x - Linux最佳生产环境离线部署

[root@slave054 local]# ps -ef|grep mysqld
root : pts/ :: grep --color=auto mysqld

[root@slave054 local]# rpm -qa |grep -i mysql

[root@slave054 local]# ps -ef|grep mysqld
root : pts/ :: grep --color=auto mysqld

[root@slave054 ~]# cd /usr/local
[root@slave054 local]# pwd
/usr/local
[root@slave054 local]#

[root@slave054 local]# ll
total
drwxr-xr-x. root root Apr bin
drwxr-xr-x. root root Apr etc
drwxr-xr-x. root root Apr games
drwxr-xr-x. root root Apr include
drwxr-xr-x. root root Apr lib
drwxr-xr-x. root root Apr lib64
drwxr-xr-x. root root Apr libexec
drwxr-xr-x. root root Apr sbin
drwxr-xr-x. root root Nov : share
drwxr-xr-x. root root Apr src
[root@slave054 local]# rz
rz waiting to receive.
zmodem trl+C ȡ % KB KB/s :: Errorssc2.-x86_64.tar.gz...[root@slave054 local]#

[root@slave054 local]# ll
total
drwxr-xr-x. root root Apr bin
drwxr-xr-x. root root Apr etc
drwxr-xr-x. root root Apr games
drwxr-xr-x. root root Apr include
drwxr-xr-x. root root Apr lib
drwxr-xr-x. root root Apr lib64
drwxr-xr-x. root root Apr libexec
-rw-r--r--. root root Nov : mysql-5.6.-linux-glibc2.-x86_64.tar.gz
drwxr-xr-x. root root Apr sbin
drwxr-xr-x. root root Nov : share
drwxr-xr-x. root root Apr src
[root@slave054 local]#

[root@slave054 local]# tar xzvf mysql-5.6.-linux-glibc2.-x86_64.tar.gz

[root@slave054 local]# ln -s mysql-5.6.-linux-glibc2.-x86_64 mysql

[root@slave054 local]# ll
total
drwxr-xr-x. root root Apr bin
drwxr-xr-x. root root Apr etc
drwxr-xr-x. root root Apr games
drwxr-xr-x. root root Apr include
drwxr-xr-x. root root Apr lib
drwxr-xr-x. root root Apr lib64
drwxr-xr-x. root root Apr libexec
lrwxrwxrwx. root root Nov : mysql -> mysql-5.6.-linux-glibc2.-x86_64
drwxr-xr-x. root root Nov : mysql-5.6.-linux-glibc2.-x86_64
-rw-r--r--. root root Nov : mysql-5.6.-linux-glibc2.-x86_64.tar.gz
drwxr-xr-x. root root Apr sbin
drwxr-xr-x. root root Nov : share
drwxr-xr-x. root root Apr src
[root@slave054 local]#

[root@slave054 local]# groupadd -g dba

[root@slave054 local]# useradd -u -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=(mysqladmin) gid=(dba) groups=(dba),(root)[root@slave054 local]# passwd mysqladmin
Changing password for user mysqladmin.
New password:
BAD PASSWORD: The password is shorter than characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@slave054 local]#

usermod -u -g dba -G root -d /usr/local/mysql mysqladmin

[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]#

[root@slave054 local]# cd /etc/
[root@slave054 etc]# vi my.cnf

my.cnf默认配置

替换配置后的效果

[root@slave054 etc]# cd -
/usr/local[root@slave054 local]# chown mysqladmin:dba /etc/my.cnf [root@slave054 local]# chmod /etc/my.cnf [root@slave054 local]# ll /etc/my.cnf
-rw-r-----. mysqladmin dba Nov : /etc/my.cnf[root@slave054 local]# ll |grep mysql
lrwxrwxrwx. root root Nov : mysql -> mysql-5.6.-linux-glibc2.-x86_64
drwxr-xr-x. root root Nov : mysql-5.6.-linux-glibc2.-x86_64
-rw-r--r--. root root Nov : mysql-5.6.-linux-glibc2.-x86_64.tar.gz[root@slave054 local]# chown -R mysqladmin:dba /usr/local/mysql[root@slave054 local]# ll |grep mysql
lrwxrwxrwx. mysqladmin dba Nov : mysql -> mysql-5.6.-linux-glibc2.-x86_64
drwxr-xr-x. root root Nov : mysql-5.6.-linux-glibc2.-x86_64
-rw-r--r--. root root Nov : mysql-5.6.-linux-glibc2.-x86_64.tar.gz[root@slave054 local]# chown -R mysqladmin:dba /usr/local/mysql-5.6.-linux-glibc2.-x86_64[root@slave054 local]# ll |grep mysql
lrwxrwxrwx. mysqladmin dba Nov : mysql -> mysql-5.6.-linux-glibc2.-x86_64
drwxr-xr-x. mysqladmin dba Nov : mysql-5.6.-linux-glibc2.-x86_64
-rw-r--r--. root root Nov : mysql-5.6.-linux-glibc2.-x86_64.tar.gz[root@slave054 local]# chmod -R /usr/local/mysql[root@slave054 local]# chmod -R /usr/local/mysql-5.6.-linux-glibc2.-x86_64[root@slave054 local]# ll |grep mysql
lrwxrwxrwx. mysqladmin dba Nov : mysql -> mysql-5.6.-linux-glibc2.-x86_64
drwxr-xr-x. mysqladmin dba Nov : mysql-5.6.-linux-glibc2.-x86_64
-rw-r--r--. root root Nov : mysql-5.6.-linux-glibc2.-x86_64.tar.gz
[root@slave054 local]#

[root@slave054 local]# su - mysqladmin
[mysqladmin@slave054 ~]$ pwd
/usr/local/mysql
[mysqladmin@slave054 ~]$ ll
total
drwxr-xr-x. mysqladmin dba Nov : bin
-rwxr-xr-x. mysqladmin dba Jan COPYING
drwxr-xr-x. mysqladmin dba Nov : data
drwxr-xr-x. mysqladmin dba Nov : docs
drwxr-xr-x. mysqladmin dba Nov : include
-rwxr-xr-x. mysqladmin dba Jan INSTALL-BINARY
drwxr-xr-x. mysqladmin dba Nov : lib
drwxr-xr-x. mysqladmin dba Nov : man
drwxr-xr-x. mysqladmin dba Nov : mysql-test
-rwxr-xr-x. mysqladmin dba Jan README
drwxr-xr-x. mysqladmin dba Nov : scripts
drwxr-xr-x. mysqladmin dba Nov : share
drwxr-xr-x. mysqladmin dba Nov : sql-bench
drwxr-xr-x. mysqladmin dba Nov : support-files
[mysqladmin@slave054 ~]$

[mysqladmin@slave054 ~]$ mkdir arch backup

[mysqladmin@slave054 ~]$ scripts/mysql_install_db
Installing MySQL system tables...-- :: [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
-- :: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OKFilling help tables...-- :: [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
-- :: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OKTo start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your systemPLEASE 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_installationwhich 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.plPlease report any problems at http://bugs.mysql.com/The latest information about MySQL is available on the web at http://www.mysql.comSupport MySQL by buying support/licenses at http://shop.mysql.comNew 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 settingsWARNING: 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 ~]$

[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 ~]$

[mysqladmin@slave054 ~]$ rpm -qa |grep gcc
gcc-c++-4.8.-.el7.x86_64
gcc-4.8.-.el7.x86_64
libgcc-4.8.-.el7.x86_64
gcc-gfortran-4.8.-.el7.x86_64
[mysqladmin@slave054 ~]$

[mysqladmin@slave054 ~]$ su - root
Password:
Last login: Mon Nov :: CST on pts/
[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 :
extras | 2.9 kB :
updates | 2.9 kB :
Package libaio-0.3.-.el7.x86_64 already installed and latest version
Nothing to do
[root@slave054 ~]#

第一次安装libaio效果

[root@slave054 ~]# su - mysqladmin
Last login: Mon Nov :: CST on pts/
[mysqladmin@slave054 ~]$ pwd
/usr/local/mysql
[mysqladmin@slave054 ~]$

[mysqladmin@slave054 ~]$ scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

执行后的效果

[mysqladmin@slave054 ~]$ su - root
Password:
Last login: Mon Nov :: CST on pts/
[root@slave054 ~]#

[root@slave054 ~]# cd /usr/local/mysql
[root@slave054 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql
[root@slave054 mysql]#

[root@slave054 mysql]# chmod +x /etc/rc.d/init.d/mysql
[root@slave054 mysql]#

[root@slave054 mysql]# chkconfig --del mysql
[root@slave054 mysql]# chkconfig --add mysql
[root@slave054 mysql]# chkconfig --level mysql on
[root@slave054 mysql]#

[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]#

[root@slave054 mysql]# su - mysqladmin
Last login: Mon Nov :: CST on pts/
[mysqladmin@slave054 ~]$ pwd
/usr/local/mysql
[mysqladmin@slave054 ~]$

安全与监听-演示

[mysqladmin@slave054 ~]$ service mysql start
Starting MySQL SUCCESS!
[mysqladmin@slave054 ~]$ service mysql status
SUCCESS! MySQL running ()
[mysqladmin@slave054 ~]$

[mysqladmin@slave054 ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.6.-log MySQL Community Server (GPL)Copyright (c) , , 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 |
+--------------------+
rows in set (0.00 sec)mysql>

[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
:: mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'.
:: mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[linux@slave054 ~]$ su - mysqladmin
Password:
Last login: Mon Nov :: CST on pts/
[mysqladmin@slave054 ~]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.6.-log MySQL Community Server (GPL)Copyright (c) , , 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 |
+--------------------+
rows in set (0.00 sec)mysql>

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
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 |
+---------------------------+
rows in set (0.00 sec)mysql> delete from user where USER='';
Query OK, rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 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
Server version: 5.6.-log MySQL Community Server (GPL)Copyright (c) , , 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 |
+--------------------+
rows in set (0.00 sec)mysql> use mysql
Database changed
mysql> update user set password=password('') where user='root';
Query OK, rows affected (0.00 sec)
Rows matched: Changed: Warnings: mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| slave054 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| :: | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
rows in set (0.00 sec)mysql> delete from user where user='';
Query OK, rows affected (0.00 sec)mysql> flush privileges;
Query OK, rows affected (0.00 sec)mysql>

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
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 |
+---------------------------+
rows in set (0.00 sec)mysql> select host,user from user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| :: | root |
| localhost | root |
| slave054 | root |
+-----------+------+
rows in set (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION;
Query OK, rows affected (0.00 sec)mysql> select host,user from user;
+-----------+------+
| host | user |
+-----------+------+
| % | root |
| 127.0.0.1 | root |
| :: | root |
| localhost | root |
| slave054 | root |
+-----------+------+
rows in set (0.00 sec)mysql> delete from user where host !='%';
Query OK, rows affected (0.00 sec)mysql> flush privileges;
Query OK, rows affected (0.00 sec)mysql> quit
Bye
[mysqladmin@slave054 ~]$

[mysqladmin@slave054 ~]$ mysql -uroot
ERROR (): 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
Server version: 5.6.-log MySQL Community Server (GPL)Copyright (c) , , 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>

# 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 PATHunset USERNAME#stty erase
set umask to
umask
PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1alias 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@slave054 ~]$ exit
logout
[linux@slave054 ~]$ su - mysqladmin
Password:
Last login: Mon Nov :: CST on pts/
slave054:mysqladmin:/usr/local/mysql:>

Error1: File '/usr/local/mysql/arch/mysql-bin.index' not found (Errcode: )
test2.localdomain:mysqladmin:/usr/local/mysql/arch:>chmod *
test2.localdomain:mysqladmin:/usr/local/mysql/arch:>chown –R mysqladmin:dba *

slave054:mysqladmin:/usr/local/mysql:>su - root
Password:
Last login: Mon Nov :: CST on pts/
[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 -- :: CST; 8s ago
Docs: man:firewalld()
Process: ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=/SUCCESS)
Main PID: (code=exited, status=/SUCCESS)Nov :: slave054 systemd[]: Starting firewalld - dynamic firewall .....
Nov :: slave054 systemd[]: Started firewalld - dynamic firewall d...n.
Nov :: slave054 systemd[]: Stopping firewalld - dynamic firewall .....
Nov :: slave054 systemd[]: 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 ~]#