MySQL

#mariadb远程

UPDATE mysql.user SET authentication_string = PASSWORD('mypassword'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = '%';

my.conf 设置bindip

#mysql修改遗忘密码

netstat -an  #查看数据库是否有连接

pkill mysqld #关闭mysql进程

mysqld_safe --skip-grant-tables & #使用mysqld_safe启动数据库并跳过密码验证

或者在my.cnf的[mysqld]中添加skip-grant-tables后重启数据库

在数据库中

use mysql；

update user set password=password("new_pass") where user="root";

 

5.7后password改为authentication_string

update user set authentication_string=password("new_pass") where user="root";

flush privileges;

关闭mysql进程

mysqld_safe & #重启mysql

#5.7添加用户与授权

CREATE USER 'dog'@'localhost' IDENTIFIED BY 'password';

use mysql；

create user 'username'@'%' identified by 'password';

%为可以冲任意主机登如数据库，可指定特定的ip

flush privileges；

grant all on database.table to 'checker'@'%';

flush privileges;

回收授权 

revoke all on check_info from 'checker'@'%';

flush privileges;

查看权限

show grants for 'checker'@'%';

 #创建数据库与用户授权

建立数据库使用utf8编码

create database platform_monitor default character set utf8 collate utf8_general_ci;

创建用户允许远程登录

create user username@"%" identified by 'password';

授权

grant select on database.table to 'user'@'%';

 

#设置字符集

查看字符集

show variables like 'char%';

配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

character_set_server=utf8
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data1/mysql
socket=/var/lib/mysql/mysql.sock
skip-host-cache
skip-name-resolve
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

　　

#index

show index from table_name
alter table table_name add index index_name (col1,col2)
alter table table_name add index (col1)

 #yum安装

yum install mariadb-server , mariadb
mkdir /data/mysql_data
chown mysql /data/mysql_data
vim /etc/my.cnf
systemctl start mariadb
systemctl enable mariadb
mysql_secure_installation