mysql常用操作

基本操作

• 结果结构化显示: \G
• 显示所有数据库: show databases
• 切换数据库: use database_name
• 显示所有表: show tables
• 显示创建表结构: show create table table\g
• 显示某个行: desc column_name
• 显示表结构: show create table table_name
• 查询状态: show status
• 查看变量: show variables
• 数据库版本: select @@version;
• 初始化数据库: sudo mysql_install_db
• 修改数据库密码: mysqladmin -u root password "newpass"

• 重启命令: ` /etc/init.d/mysql start

  stop

  restart

  reload

  force-reload`

• 查看版本: show variables like 'version%';
• 在mysql内修改密码: set password for 'root'@'localhost'=password('free');
• 修改配置刷新: flush privileges;
• 查看mysql编码: show variables like 'character%';
• 清空table(id从0开始): truncate table tablename
• 查看当前数据库编码: SHOW CREATE DATABASE db_name;
• 查看表编码: SHOW CREATE TABLE tbl_name;
• rake db:drop db:create db:migrate
mysqldump --user="username" --password="password" --skip-triggers --compact --no-create-info --ignore-table="database_name.schema_migrations" --ignore-table="database_name.table_populated_through_migration" "database_name" > "dump.sql"
• 修改数据库字段类型: alter table A(表名) modify b(字段名) varchar(50)(类型);

权限控制

• 给用户授权: grant all privileges on *.* to 'abc'@'%';
• 取消权限: revoke all on *.* from 'abc'@'%';
• 试图连接数据库的次数: show status like 'Connections';

性能检测及优化

• 慢查询的次数: show status like 'Slow_queries';
• 修改慢查询时间: set long_query_time=2
• 查看查询缓存情况: show variables like '%query_cache%';
• 查看慢查询日志: msqldumpslow -t 3 xx/mysql-slow.log | more
• 查看sql执行计划: explain sql
• 使用alter table时modify column会重建表, alter column直接修改.frm文件
• innodb引擎支持自适应哈希索引,当索引字段很大时可以新建该列哈希值用于检索

触发器

• 创建: CREATE TRIGGER trigger_name trigger_time(触发时机before,after) trigger_event(触发事件insert,update,delete) ON tbl_name(触发器的表名) FOR EACH ROW trigger_stmt(触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句)
• 查看: SHOW TRIGGERS [FROM database];
• 删除: DROP TRIGGER [IF EXISTS] [database.]trigger_name
• 用例(维护哈希索引表):

DELIMITER //
create trigger table_name_crc_ins before insert on table_name for each row BEGIN set NEW.url_crc=crc32(NEW.url); END; 
//
create trigger table_name_crc_upd before update on table_name for each row BEGIN set NEW.url_crc=crc32(NEW.url); END; 
//
DELIMITER ;

存储过程

存储函数(只有一个返回值时使用)

• 和存储过程差别: 是否用return语句返回值

mysql远程访问。

• 进入远程数据库输入: grant select,insert,update,delete(ALL PRIVILEGES) on 数据库.表名(所有表*) to root(用户名)@'%(ip)' identified by '密码'; 。
• 进入远程数据库输入: netstat -an |grep 3306 查看mysql状态。
• 确认防火墙的3306端口开着(本地输入): ufw status。
• 确认本地可以访问远程数据库: telnet ip 3306
• /etc/mysql/my.cnf 数据库配置文件中 bind-address=127.0.0.1 改为 0.0.0.0(或注释掉)
• 重启: sudo/etc/init.d/mysql restart(stop,start), sudo restart mysql, sudo service mysql restart
• iptables防火墙

导入导出数据库

• 导出数据成 sql 文件: mysqldump -h localhost -u root -p database_name > ~/database.sql
• 复制 sql 文件到本地：
  scp -P 1000 user@xxx.com:/home/deployer/database.sql ~/
scp -r -i testkey.pem.txt ubuntu@xxx.com:/home/ubuntu/database.sql /home/ubuntu/Data
• 将 sql 文件导入到数据库: mysql -h localhost -u root -p database_name < ~/database.sql
• 不同数据库追加数据: INSERT INTO db1.tab1(title, content) select title, content from db2.tab2;

mysql设置中文

• 查看编码格式: show variables like 'character%';
• 修改/etc/my.cnf，分别在[client],[mysqld],[mysql]字段里添加default-character-set=utf8。
• 重启 service mysql restart

解决与rails的兼容问题

• 类型不匹配

# initializers/mysqlpls.rb

require 'active_record/connection_adapters/abstract_mysql_adapter'

module ActiveRecord
  module ConnectionAdapters
    class AbstractMysqlAdapter
      NATIVE_DATABASE_TYPES[:string] = { :name => "varchar", :limit => 191 }
    end
  end
end

报错解决方法

• Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’: alter table dbname.tablename convert to character set utf8 collate utf8_general_ci;
• 关于一次导入数据提示的MySQL server has gone away: set global max_allowed_packet = 2*1024*1024*10
• ActiveRecord::StatementInvalid: Mysql2::Error: Table ‘performance_schema.session_variables’ doesn’t exist: SHOW VARIABLES LIKE ‘character_set_database’: ` mysql_upgrade -u root -p –force`后重启
• 解决整形限制 Rails 2.1 broke my mysql foreign keys!

MySQL密码的恢复方法

有可能你的系统没有 safe_mysqld 程序(比如我现在用的 ubuntu操作系统, apt-get安装的mysql) , 下面方法可以恢复

1. 停止mysqld； /etc/init.d/mysql stop (您可能有其它的方法,总之停止mysqld的运行就可以了)

2. 用以下命令启动MySQL，以不检查权限的方式启动； mysqld –skip-grant-tables &
3. 然后用空密码方式使用root用户登录 MySQL； mysql -u root
4. 修改root用户的密码； mysql> update mysql.user set password=PASSWORD(‘newpassword’) where User=’root’; mysql> flush privileges; mysql> quit 重新启动MySQL /etc/init.d/mysql restart 就可以使用新密码 newpassword 登录了。