mysql 记录号_记录：mysql账号操作

修改mysql root密码：

mysql -uroot -p;

mysql> use mysql;

mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

mysql> FLUSH PRIVILEGES;

1、以管理员身份登录mysql

mysql -uroot -p

#创建数据库，默认UTF-8编码

CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

use mysql

#创建dba用户可以对所有数据库管理，并且远程管理

create user 'dba'@'%' identified by 'testpassword';

FLUSH PRIVILEGES;

grant all privileges on *.* to dba@'%' identified  by 'oiweDBAfw(@)(23kxl';  ##加上WITH GRANT OPTION, 意思是使该帐号有赋予其他帐号的权限，小心使用，一般不使用，只有root使用

FLUSH PRIVILEGES;

#创建某一个网站的db用户

create user 'testuser'@'localhost' identified by 'testpassword';

flush privileges;

grant all privileges on testdb.* to test@localhost identified  by 'testpasswd';

flush privileges;

9. 更新密码

./mysqladmin -u root -p oldpassword newpasswd(记住这个命令是在/usr/local/mysql/bin中外部命令)

-------------------------------

二：MYSQL高效导入sql文件技巧：

先退出登陆状态下的mysql帐号，再命令

exit;

mysql dbname < sql.sql -uroot -p;

如果root做了登陆限制的还要加上-h127.0.0.1，比如：mysql dbname < sql.sql  -h127.0.0.1 -uroot -p;

三：mysql.cnf配置文件：

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysql]

socket = /dev/shm/mysql.sock

[mysqld]

port = 3388

socket = /dev/shm/mysql.sock

#使用skip-name-resolve取消域名反解析，可以提高速度，这样一来的话在mysql.user表中，使用localhost就无法解析，要改成127.0.0.1的IP形式，PHP文件中也要使用IP形式

#同样在服务器本机登录mysql命令行，也需要加上-h1270.0.1参数

skip-name-resolve

slow_query_log = 1

long_query_time = 2

slow_query_log_file = /home/jianguo/log/mysql-slowquery.log

server-id = 1

log-bin = syslog

#binlog-do-db = hecart

#binlog-do-db = demodb

expire_logs_days = 3

event_scheduler = OFF

max_connections = 1024

thread_cache_size = 8

explicit_defaults_for_timestamp = true

# 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 = 200M

innodb_thread_concurrency = 4

# Remove leading # to set options mainly useful for reporting servers.

join_buffer_size = 200M

sort_buffer_size = 300M

read_rnd_buffer_size = 300M

tmp_table_size = 50M

key_buffer_size = 300M

query_cache_size = 500M

max_join_size = 350M

max_heap_table_size = 25M

max_binlog_cache_size = 200M

max_binlog_stmt_cache_size = 20M

sql-mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

-------------------------------------------------------------------------------------------------