数据库初始化
默认情况下,数据已经初始化好,数据可参见默认配置文件/etc/my.cnf
在其他位置重新初始化MySQL数据库:
basedir是mysql的安装根目录,ldata是数据初始化的目录
mysql_install_db --basedir=/ --ldata=./data
相关提示:
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place foryour system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!Todoso, start the server, then issue the following commands:
mysqladmin-u root password 'new-password'mysqladmin-u root -h xxx.xxx.xxx.xxx password 'new-password'Alternatively you can run:
mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created bydefault. This isstrongly recommendedforproduction servers.
See the manualformore instructions.
You can start the MySQL daemon with:
cd . ; mysqld_safe&You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQLisavailable on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file/etc/my.cnf on the system.
Becausethis file might be inuse, it was not replaced,
but was usedin bootstrap (unless you used --defaults-file)
and when you later start the server.
Thenew default config file was created as /etc/my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file/etc/my.cnf exists on the system
This file will be read bydefaultby the MySQL server
If youdo not want to use this, either remove it, or use the--defaults-file argument to mysqld_safe when starting the server
数据库实例配置
my.cnf配置文件内容
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
# Remove leading # andset to the amount of RAM forthe most important data
# cachein 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
log-error=//log/mysql.log.err
general_log=ON
general_log_file=//log/mysql_general.log
slow_query_log=ON
long_query_time=10slow_query_log_file= //log/mysql_slow_query.log
# These are commonlyset, remove the # and set asrequired.
# basedir=.....
datadir=//data
port= 3306# server_id=.....
socket= //mysql.3306.sock
pid-file =//mysql.3306.pid
# Remove leading # toset options mainly useful forreporting servers.
# The server defaults are fasterfortransactions and fast SELECTs.
# Adjust sizesasneeded, experiment to find the optimal values.
# join_buffer_size=128M
# sort_buffer_size=2M
# read_rnd_buffer_size=2M
user=mysqlsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
数据库启动
mysqld_safe --defaults-file=//my.cnf
数据库登录
mysql --socket=mysql.3306.sock
数据库实例管理
查看数据库状态
mysqladmin --socket=mysql.3306.sock status
更改root密码:
mysqladmin -u root password root --socket=mysql.3306.sock
数据库关闭
mysqladmin -proot -uroot --socket=mysql.3306.sock shutdown
用户和权限管理
名为mysql的数据库中存放这元数据,其中use表与用户和权限有关。
use表的Host User Password列与用户登录有关,这三列可以确定登录用户的身份。
use表的Select_priv、Insert_priv等以priv结尾的列与用户权限有关,Y表示对所有表生效,N表示不对所有表生效。
使用数据库root用户登录数据库,并使用mysql数据库
mysql -uroot -proot --socket=mysql.3306.sock -D mysql
新建普通用户
create user 'username'@'host' identified by 'password'
其中host可以由%代替,表示对所有host登录的都适用。
或者
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','username',PASSWORD('password'),'','','');
FLUSH PRIVILEGES
或者
GRANT SELECT ON *.* TO 'username'@'%' identified by 'password';
其中*.*表示对所有数据库的所有表,这条语句可以在创建用户的同时给权限。
用户权限
查看权限
SHOW GRANT
赋予权限
GRANT SELECT,UPDATE,DELETE ON *.* TO 'username'@'%'
收回权限
REVOKE ALL ON *.* TO 'username'@'%'
FLUSH PRIVILEGES
删除用户
DROP USER 'username'@'%'
或者
DELETE FROM mysql.user WHERE Host = '%' AND User = 'username'
修改密码
使用命令mysqladmin -u -username -p password "new_password"
或者改表
UPDATE user SET Password = PASSWORD('new_password') WHERE USER = 'username' and Host = '%'
FLUSH PRIVILEGES
或者修改当前用户密码
SET PASSWORD = PASSWORD("new_password");
修改其他用户密码
SET PASSWORD FOR 'username'@'%'=PASSWORD("new_password")
发布地址:www.cnblogs.com/qiusuo/p/9451717.html