sql

mysqld的常用命令：

2.1、启动mysqld服务：systemctl start mysqld

2.2、关闭mysqld服务：systemctl stop mysqld

2.3、重启mysqld服务：systemctl restart mysqld

2.4、查看mysqld服务：systemctl status mysqld

2.5、设置开机自启动mysqld服务：systemctl enable mysqld

2.6、关闭开机自启动mysqld服务：systemctl disable mysqld

centos7 mysql命令

service mysql start
service mysql stop
service mysql restart
service mysql status

my.cnf

[mysqld]
connect_timeout = 15
wait_timeout = 20
interactive_timeout = 20
innodb_buffer_pool_size = 128M
log_bin

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-error = /var/log/mysql/error.log
pid-file = /var/log/mysql/mysql.pid
port = 3306
server_id = 1
socket = /tmp/mysql.sock

bind-address = 0.0.0.0
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

添加 root用户，设置密码相关

Mysql误删root用户解决办法

注释掉 my.cnf中的 skip-grant-tables参数，重启数据库

mysql> use mysql

mysql> select host,user from user;

mysql>insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';
mysql>update user set select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y',Event_priv='y',Trigger_priv='y',Create_tablespace_priv='y' where user='tarsnode';

使用下列命令重新登陆，设置root的密码

mysql> mysql -u root;
//mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'lwFaChIQif5L4qOR2cZB3YsJfE5Wy4ujH';

mysql> update mysql.user set authentication_string=password('lwFaChIQif5L4qOR2cZB3YsJfE5Wy4ujH') where user='root' and host='localhost';

mysql> flush privileges;
mysql> quit;

select host,user,authentication_string from user;
select * from mysql.user where user='tarsnode'\G;
flush privileges;

CREATE USER 'tarsnode'@'localhost' IDENTIFIED BY 'lwFaChIQif5L4qOR2cZB3YsJfE5Wy4ujH';
ALTER USER 'tarsnode'@'localhost' IDENTIFIED WITH mysql_native_password BY 'lwFaChIQif5L4qOR2cZB3YsJfE5Wy4ujH';
update user set select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y',Event_priv='y',Trigger_priv='y',Create_tablespace_priv='y' where user='tarsnode';

update user set host='localhost' where user='root';
CREATE USER 'TCvm'@'%' IDENTIFIED BY 'ORf$CZB^YsJ5ylE4*iu&Hl@C^IWYsf5L6*OR$CZB^YsJfaZE@FaCy5LOR$5L1Iu&H';
update user set select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='n',drop_priv='n',reload_priv='y',shutdown_priv='n',Process_priv='n',file_priv='n',grant_priv='n',References_priv='n',index_priv='n',create_user_priv='n',show_db_priv='y',super_priv='n',create_tmp_table_priv='n',Lock_tables_priv='n',execute_priv='n',repl_slave_priv='n',repl_client_priv='n',create_view_priv='n',show_view_priv='n',create_routine_priv='n',alter_routine_priv='n',Event_priv='n',Trigger_priv='n',Create_tablespace_priv='n' where user='TCvm';

lwFaChIQif5L4qOR2cZB3YsJfE5Wy4ujH

macOSSierra错误：13 - 权限被拒绝(phpmyadmin site on macOSSierra errno: 13 - Permission denied)

当/ usr / local / mysql文件夹没有足够的权限时，会发生此错误。您可以使用以下命令更改权限来解决此问题

chmod -R 755 ./mysql

This is error occurs when /usr/local/mysql folder doesn't have not enough permission. You can fix this by changing permissions using the following command

chmod -R 755 ./mysql

MySQL常用指令（备查）

https://www.cnblogs.com/qmfsun/p/4845837.html

https://www.jianshu.com/p/8f8d8161631f

//mysql 5.7.34 下载地址

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.34.tar.gz

mysql5.7.9 源码安装 (转）

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost

进入support-files目录