mysql日常管理

环境:

OS:AIX 5.3

 

-----------------------------系统部分-------------------------------------

1.无法登陆mysql

[hxl/root]#mysql -h hxl -uroot -pmysql
ERROR 1045 (28000): Access denied for user 'root'@'hxl' (using password: YES)

grant all on mysql.* to 'root'@'hxl' identified by 'mysql';

 

2.启动mysql

[hxl/root]#mysqld_safe
121129 20:08:44 mysqld_safe Logging to '/usr/local/mysql/data/hxl.err'.
121129 20:08:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

 

3.关闭mysql
[hxl/root]#mysqladmin -p -u root -h hxl shutdown
Enter password:

 

4.启动mysql

cd /usr/local/mysql/bin
mysqld_safe --user=mysql &

 

5.修改数据库默认引擎

vi /etc/my.cnf
在[mysqld]条目下添加
default-storage-engine = InnoDB
重启动mysql
cd /usr/local/mysql/bin
mysqladmin -p -u root -h hxl shutdown
mysqld_safe --user=mysql &

6.修改表的默认引擎

alter table books ENGINE=InnoDB

 

7.查看数据库的默认启动选项

[hxl/root]#mysqld --print-defaults
mysqld would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --skip-locking --key_buffer_size=384M --max_allowed_packet=1M --table_open_cache=512 --sort_buffer_size=2M --read_buffer_size=2M --read_rnd_buffer_size=8M --myisam_sort_buffer_size=64M --thread_cache_size=8 --query_cache_size=32M --thread_concurrency=8 --default-character-set=utf8 --max_connections=500 --max_connect_errors=1000 --default-storage-engine=InnoDB --log-bin=mysql-bin --server-id=1

 

--------------------------------数据库管理部分---------------------------

1.show databases;            -- 显示数据库

2.show select version();    -- 显示当前数据库的版本
3.show engines                -- 显示数据库引擎
4.show variables like 'have%';   -- 查找模糊匹配字符串变量
5.select database();-- 显示当前使用的数据库

--------------------------------用户部分----------------------------------

创建数据库
1.create database dev;

2.创建数据库并指定字符集
create database dev01 default charset utf8 default collate utf8_general_ci;

创建用户
方法1:
grant ALL on dev.* to 'dev'@'%' identified by 'mysql' WITH GRANT OPTION; --任何远程机器都可以登录
grant ALL on dev.* to 'dev'@'localhost' identified by 'mysql' WITH GRANT OPTION; --本机可以登录

方法2:
创建用户
create user dev01;
创建密码
set password for 'dev01'=password('mysql');
设置权限
grant ALL on dev.* to 'dev01'@'%' identified by 'mysql' WITH GRANT OPTION;
grant ALL on dev.* to 'dev01'@'localhost' identified by 'mysql' WITH GRANT OPTION;
grant ALL on dev.* to hxl@'192.168.56.%' identified by 'mysql' WITH GRANT OPTION;

修改密码
SET SQL_SAFE_UPDATES = 0;
update mysql.user set password=password('mysql01') where user='dev01' and host='localhost';
flush privileges;

删除用户
DELETE FROM mysql.user WHERE User='dev' and Host='*.*';
或是
drop user 'dev'@'%.%'


刚安装好mysql后为root用户创建密码
-bash-4.1$ mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
mysql> FLUSH PRIVILEGES;


---------------------------------------权限---------------------------------------------
1.查看表权限
select * from mysql.tables_priv;
2.查看列权限
select * from mysql.columns_priv;
3.db权限表
select * from mysql.db;

-------------------------------------资源限制-------------------------------------------1.设置用户每小时连接数
grant usage on dev.* TO 'hxl'@'%' with max_queries_per_hour 100;

------------------------binlog部分---------------------------------------------------
1.查看日志里的事件
show binlog events in 'mysql-bin.009299';
2.查看日志内容
/home/richdm/mysql/bin/mysqlbinlog --start-datetime='2017-06-10 10:00:00' --stop-datetime='2017-06-11 00:00:00' -d ngoss_dim /home/richdm/var/mysql/mysql-bin.009166