MySQL

创建用户，分配权限

一般的应用用户只分配select,insert,update,execute权限，不分配delete权限。并且每个应用都要有自己的数据库用户名，方便DBA定位SQL语句是哪个应用产生的。

• 允许内网其他主机访问

  grant select,insert,update,execute on vbam.* to 'member_server'@'192.168.1.0/255.255.255.0' identified by 'Abc12345';

• 允许本机访问

  grant select,insert,update,execute on vbam.* to 'member_server'@'localhost' identified by 'Abc12345';

  grant select,insert,update,execute on vbam.* to 'member_server'@'127.0.0.1' identified by 'Abc12345';

• 分配全部权限

  grant all privileges on *.* to root@'192.168.1.0/255.255.255.0' identified by 'Abc12345';

• 最低权限的管理员账号

  GRANT select,insert,update,execute,create,alter,CREATE USER ON *.* TO 'dba'@'%' identified by 'Abc12345' WITH GRANT OPTION;

  GRANT select,insert,update,execute,create,alter,CREATE USER ON *.* TO 'dba'@'localhost' identified by 'Abc12345' WITH GRANT OPTION;

  GRANT select,insert,update,execute,create,alter,CREATE USER ON *.* TO 'dba'@'127.0.0.1' identified by 'Abc12345' WITH GRANT OPTION;

• 让权限立即生效

  FLUSH PRIVILEGES

数据库迁移

1. 从源库导出schema: mysqldump -uroot -pAbc12345 --databases vbam --no-data --routines > /tmp/vbam.sql
2. 从源库导出schema和数据: mysqldump -uroot -pAbc12345 --databases vbam --routines > /tmp/vbam.sql
3. 在目标库导入sql： mysql -uroot -pAbc12345 < /tmp/vbam.sql
4. 从源库导出某张表的schema和数据: mysqldump -uroot -pAbc12345 vbam setting_region > /tmp/region

5. 导出多张表的表结构和数据:

   tables="";
   for table in $(mysql -uroot -pAbc12345 -AN -e "show tables from vbam where Tables_in_vbam like 'ACT_%';");
   do
   tables="$tables $table";
   done
   echo $tables;
   mysqldump -uroot -pAbc12345 vbam $tables > /tmp/111
   

日志相关的参数

1. 创建日志目录： mkdir -p /var/log/mysql
2. (可选)如果启动mysql的进程不是root，而是mysql,需要更改日志目录的属主: chown -R mysql /var/log/mysql

3. 修改/etc/my.cnf

   [mysqld]
   log_error=/var/log/mysql/mysql_error.log
   slow_query_log=1
   slow_query_log_file=/var/log/mysql/mysql-slow.log
   long_query_time=5
   log_queries_not_using_indexes=1
   innodb_print_all_deadlocks=1
   

4. 重启mysql: service mysqld restart

online backup

1. 备份主库数据：mysqldump -uroot -p --all-databases --triggers --routines --events --master-data=2 --single-transaction --flush-logs >/tmp/alldb.sql
2. 恢复数据:
   • mysql -h127.0.0.1 -P13306 -uroot < /tmp/alldb.sql
   • flush privileges; 不执行这条语句用户和权限不会生效
3. 查看binlog文件列表： show binary logs;
4. 查看binlog文件内容：
   • show binlog events in 'mysqlslave-bin.000040' limit 10;
   • mysqlbinlog --no-defaults --set-charset=utf8 --start-position="120" --stop-position="9404693" mysqlslave-bin.000039

数据库重命名

1. 导出旧库的数据: mysqldump -uroot -pAbc12345 vbam --routines --single-transaction --set-gtid-purged=OFF> /tmp/vbam.sql
2. 创建新的数据库: mysql -uroot -pAbc12345 -e "create database homsom"
3. 将老库数据导入新库: mysql -uroot -pAbc12345 homsom < /tmp/vbam.sql