linux mysql常用操作

linux下MySQL停止和重启

一、启动方式
1、使用linux命令service 启动：
service mysqld start
2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld start
3、使用 safe_mysqld 启动：
safe_mysqld&

二、停止
1、使用 service 启动：
service mysqld stop
2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld stop
3、 mysqladmin shutdown

三、重启
1、使用 service 启动：
service mysqld restart
2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld restart

备注：查看mysql端口是否已经使用，使用netstat -anp 命令查看服务器端口使用情况。

没有设置表名不区分大小写：

运行环境：centos 7.5+MySQL8.0.12
 
背景：
使用YUM或者RPM按照默认的参数安装后要修改不区分大小写参数报错
 
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
 
解决办法：
1.若已经运行了一段时间产生了数据则需要备份数据
2.若是新安装没多久没有产生重要数据则可重新配置参数重新安装。

查看MySQL官方文档，有记录：

lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

只有在初始化的时候设置 lower_case_table_names=1才有效，比如：

--initialize --lower-case-table-names=1
mysqld --user=mysql --lower-case-table-names=1 --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/my.cnf

 [mysqld]
 lower_case_table_names = 1


 
我这里由于是体验mysql8.0.12版本没有产生数据。
1.重新配置参数文件my.cnf，在MySQL启动之前将参数lower_case_table_names设置为不区分大小写：
# cat /etc/my.cnf | grep -v ^#
 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps                 = SYSTEM
lower_case_table_names         = 1
default-authentication-plugin  =mysql_native_password
2.停掉运行的MySQL进程删除默认datadir下的文件,清空错误日志文件便于重启查看：
# systemctl stop mysqld
# rm -rf /var/lib/mysql/*
# echo '' > /var/log/mysqld.log 
 
3.重启MySQL 查看临时密码 登录数据库：
# systemctl start mysqld
# cat /var/log/mysqld.log | grep -i password
2018-07-29T00:29:19.851620+08:00 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !U5uL5VU1e4g
 
# mysql -p -S /var/lib/mysql/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.12
 
Copyright (c) 2000, 2018, 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 variables like '%valid%password%';
登录进去之后首先要求修改root用户的密码：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@'localhost' identified by 'Oracle@2018';
Query OK, 0 rows affected (0.04 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'valid%pass%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
 
按照密码强度设置用户的密码。
 
4.若不想设置太复杂的密码 可以首先设置复杂的密码之后卸载密码强度认证的组件：
mysql>  UNINSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.10 sec)
 
mysql> show variables like 'valid%pass%';
Empty set (0.00 sec)
 
-- 删除之后再把密码修改为简单的，卸载之后重启mysql也没有密码强度认证了。
mysql> alter user root@'localhost' identified by 'oracle';
Query OK, 0 rows affected (0.02 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
5.设置可以远程登录的用户：
mysql> create user root@'%' identified by 'oracle';
Query OK, 0 rows affected (0.06 sec)
 
mysql> grant all privileges on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.04 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
6.查看配置的区分大小写的参数是否生效：
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.01 sec)
7.查看8.0版本默认的字符集和排序规则：
mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
 
mysql> show variables like '%coll%';                           
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
 
mysql> 
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)