vlambda博客
学习文章列表

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

[root@testsrv ~]# useradd -g mysql -u 2000 -s /sbin/nologin -d /usr/local/mysql -MN mysql

[root@testsrv ~]# echo "vm.swappiness=1" >> /etc/sysctl.conf[root@testsrv ~]# sysctl -p

[root@testsrv ~]# echo '* soft nofile 65535' >> /etc/security/limits.conf[root@testsrv ~]# echo '* hard nofile 65535' >> /etc/security/limits.conf

[root@testsrv ~]# echo "ulimit -n 65535" >> /etc/rc.local[root@testsrv ~]# ulimit -n 65535

[root@testsrv ~]# echo deadline > /sys/block/sda/queue/scheduler[root@testsrv ~]# grubby --update-kernel=ALL --args="elevator=deadline"

[root@testsrv ~]# sed -i '/^numa/c numa=off' /etc/default/grub[root@testsrv ~]# grub2-mkconfig -o /etc/grub2.cfg >/dev/null

[root@testsrv ~]# vi /etc/selinux/config修改内容为SELINUX=disabled

[root@testsrv ~]# reboot

[root@testsrv ~]# tar zxf /root/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /opt/[root@testsrv ~]# ls /opt/mysql-5.7.30-linux-glibc2.12-x86_64bin data docs include lib LICENSE man README share support-files

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

[root@testsrv ~]# chown -R mysql:mysql /data/mysql/mysql3306/{data,logs,tmp}

/data/mysql/└── mysql3306 #实例端口号作为实例DATA DIR的一部分，便于管理和识别 ├── data #实例数据文件的存放目录（datadir就是它）    ├── logs   #实例日志文件的存放目录 └── tmp #实例的临时文件目录

[root@testsrv ~]# vi /data/mysql/mysql3306/my3306.cnf

[mysqld]user                                =mysqlbasedir                             =/opt/mysql-5.7.30-linux-glibc2.12-x86_64/datadir                             =/data/mysql/mysql3306/data/server_id                           =1003306port                                =3306bind-address                        =127.0.0.1character_set_server                =utf8mb4explicit_defaults_for_timestamp     =onlog_timestamps                      =systemlower_case_table_names              =1default_time_zone                   ='+08:00'socket                              =/data/mysql/mysql3306/tmp/mysql.socksecure_file_priv                    =/data/mysql/mysql3306/tmp/binlog_format                       =rowlog_bin                             =/data/mysql/mysql3306/logs/mysql-binbinlog_rows_query_log_events        =onlog_slave_updates                   =onlog_error                           =/data/mysql/mysql3306/logs/error.loggeneral_log                         =offgeneral_log_file                    =/data/mysql/mysql3306/logs/general.logslow_query_log                      =onslow_query_log_file                 =/data/mysql/mysql3306/logs/slow_query.loglog_queries_not_using_indexes       =onlong_query_time                     =1.000000gtid_mode                           =onenforce_gtid_consistency            =on

[root@testsrv ~]# ldd /opt/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqld

[root@testsrv ~]# ln -sf /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql[root@testsrv ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile

[root@testsrv ~]# . /etc/profile[root@testsrv ~]# which mysql/usr/local/mysql/bin/mysql

[root@testsrv ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize

[root@testsrv ~]# ls /data/mysql/mysql3306/dataauto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql private_key.pem server-cert.pem sysca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ib_logfile2 performance_schema public_key.pem server-key.pem
[root@testsrv ~]# cat /data/mysql/mysql3306/logs/error.log……2020-07-10T17:43:51.243252+08:00 0 [Warning] CA certificate ca.pem is self signed.2020-07-10T17:43:51.551619+08:00 1 [Note] A temporary password is generated for root@localhost: QsJqBL+7*;de

简单语法：mysqld --defaults-file=${mycnf_file} &--defaults-file 指定mysql cnf 配置文件路径

[root@testsrv ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

[root@testsrv ~]# ps -ef | grep mysqldmysql 527 465 1 09:20 pts/0 00:00:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf

简单语法：mysql -u root -p123 -h 127.0.0.1 -P 3306 -S xxxx.sock-u mysql user，不指定则以当前OS username-p mysql user的密码-h mysql实例的服务器地址/IP，可能与-P连用-P mysql实例的服务器端口，默认为3306-S 本地socket方式登陆时用来指定socket文件地址，用-S就不需要-h 和-P了

[root@testsrv ~]# mysql -S /data/mysql/mysql3306/tmp/mysql.sock -u root -pEnter password: # 此处密码不会显示，将初始化时errorlog中的临时密码：QsJqBL+7*;de 输入或粘贴进去即可Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.30-log
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
先退出mysql> exitBye

[root@testsrv ~]# mysql -h 127.0.0.1 -u root -p Enter password: # 此处密码不会显示，将初始化时errorlog中的临时密码：QsJqBL+7*;de 输入或粘贴进去即可Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.30-log
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user user() identified by 'hellomysql';Query OK, 0 rows affected (0.06 sec)

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)

[root@testsrv ~]# rm -fr /data/mysql/mysql3306/data/*[root@testsrv ~]# rm -fr /data/mysql/mysql3306/logs/*[root@testsrv ~]# rm -fr /data/mysql/mysql3306/tmp/*

[root@testsrv ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure

[root@testsrv ~]# ls /data/mysql/mysql3306/dataauto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql private_key.pem server-cert.pem sysca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ib_logfile2 performance_schema public_key.pem server-key.pem
[root@testsrv ~]# cat /data/mysql/mysql3306/logs/error.log……2020-07-10T17:33:54.606511+08:00 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

[root@testsrv ~]# mysql -S /data/mysql/mysql3306/tmp/mysql.sock -u rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.30-log
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

随机密码：mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize无密码：mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure

mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

mysql -S /data/mysql/mysql3306/tmp/mysql.sock -u root -p

mysql> alter user username identified by 'newpassword';如果修改当前用户密码，username可以直接使用user() 函数。

mysql> \s （清晰简要）或mysql> show status\G （长篇大论）

mysql> shutdown ; exit;