#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