mysql常用解决方案

mysql常用解决方案

1.删除了mysql数据库user表记录，会导致用户不能正常登录。

此时将mysqld 以skip-grant-tables启动，忽略权限。
进入到mysql库中，执行以下脚本。
就可以用root/bill登录了

    
 	
  1. /*
    2. 
 	
  2. Navicat MySQL Data Transfer
    3. 
 	
    4. 
 	
  4. Source Server         : localhost
    5. 
 	
  5. Source Server Version : 50703
    6. 
 	
  6. Source Host           : localhost:3306
    7. 
 	
  7. Source Database       : mysql
    8. 
 	
    9. 
 	
  9. Target Server Type    : MYSQL
    10. 
 	
  10. Target Server Version : 50703
    11. 
 	
  11. File Encoding         : 65001
    12. 
 	
    13. 
 	
  13. Date: 2015-06-13 20:07:50
    14. 
 	
  14. */
    15. 
 	
    16. 
 	
  16. SET FOREIGN_KEY_CHECKS=0;
    17. 
 	
    18. 
 	
  18. -- ----------------------------
    19. 
 	
  19. -- Table structure for `user`
    20. 
 	
  20. -- ----------------------------
    21. 
 	
  21. DROP TABLE IF EXISTS `user`;
    22. 
 	
  22. CREATE TABLE `user` (
    23. 
 	
  23.   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
    24. 
 	
  24.   `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
    25. 
 	
  25.   `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
    26. 
 	
  26.   `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    27. 
 	
  27.   `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    28. 
 	
  28.   `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    29. 
 	
  29.   `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    30. 
 	
  30.   `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    31. 
 	
  31.   `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    32. 
 	
  32.   `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    33. 
 	
  33.   `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    34. 
 	
  34.   `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    35. 
 	
  35.   `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    36. 
 	
  36.   `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    37. 
 	
  37.   `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    38. 
 	
  38.   `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    39. 
 	
  39.   `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    40. 
 	
  40.   `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    41. 
 	
  41.   `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    42. 
 	
  42.   `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    43. 
 	
  43.   `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    44. 
 	
  44.   `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    45. 
 	
  45.   `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    46. 
 	
  46.   `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    47. 
 	
  47.   `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    48. 
 	
  48.   `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    49. 
 	
  49.   `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    50. 
 	
  50.   `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    51. 
 	
  51.   `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    52. 
 	
  52.   `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    53. 
 	
  53.   `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    54. 
 	
  54.   `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    55. 
 	
  55.   `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
    56. 
 	
  56.   `ssl_cipher` blob NOT NULL,
    57. 
 	
  57.   `x509_issuer` blob NOT NULL,
    58. 
 	
  58.   `x509_subject` blob NOT NULL,
    59. 
 	
  59.   `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
    60. 
 	
  60.   `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
    61. 
 	
  61.   `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
    62. 
 	
  62.   `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
    63. 
 	
  63.   `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
    64. 
 	
  64.   `authentication_string` text COLLATE utf8_bin,
    65. 
 	
  65.   `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    66. 
 	
  66.   PRIMARY KEY (`Host`,`User`)
    67. 
 	
  67. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
    68. 
 	
    69. 
 	
  69. -- ----------------------------
    70. 
 	
  70. -- Records of user
    71. 
 	
  71. -- ----------------------------
    72. 
 	
  72. INSERT INTO `user` VALUES ('%', 'root', '*29A1BB43D3B9EB42028B4566E4836353285B9395', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0', 'mysql_native_password', '', 'N');
    73. 
 	
  73. INSERT INTO `user` VALUES ('127.0.0.1', 'root', '*29A1BB43D3B9EB42028B4566E4836353285B9395', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0', 'mysql_native_password', '', 'N');
    74. 
 	
  74. INSERT INTO `user` VALUES ('::1', 'root', '*29A1BB43D3B9EB42028B4566E4836353285B9395', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0', 'mysql_native_password', '', 'N');
    75. 
 	
  75. INSERT INTO `user` VALUES ('localhost', '', '', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', '0', '0', '0', '0', 'mysql_native_password', null, 'N');
    76.

2.授权
#(执行下面的语句  *.*:所有库下的所有表   %：任何IP地址或主机都可以连接)
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘123’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
mysql导入导出包括函数或者存储过程
 
1.mysql导出整个数据库
      mysqldump -h hostname -u username -p  databasename > backupfile.sql
      如果root用户没用密码可以不写-p，当然导出的sql文件你可以制定一个路径，未指定则存放在mysql的bin目录下  www.2cto.com  
 
2.mysql导出数据库一个表
　　mysqldump -hhostname -uusername -ppassword database  tablename> 导出的文件名
　　mysqldump -hlocalhost -uroot hqgr t_ug_user> user.sql
3.mysql导出一个数据库结构
　　mysqldump -hhostname -uusername -ppassword  -d –add-drop-table databasename>d:hqgrstructure.sql
　　-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
4.如果需要导出mysql里面的函数或者存储过程
      mysqldump -h  hostname -u  username   -ntd -R   databasename  > backupflie.sql  （包括存过过程和函数一次性完整导出）
     
        其中的 -ntd 是表示导出表结构和数据；-R是表示导出函数、存储过程
 
     可以参照mysqldump –help
 
mysql常用导入数据的命令：
      mysql数据库导入导出：
      mysqldump -u 用户名 -p 数据库名 > 数据库名.sql
      如：
   mysqldump -u root   -p         testdb  > testdb.sql (不包括存储过程和函数)
   mysqldump -u root   -p  -R   testdb  > testdb.sql (**包括存储过程和函数**)
 
     MySQL source命令向数据库中导入数据：
     mysql>use testdb;
    mysql>set names  utf8;
     mysql>source /tmp/bdc.sql;
                 
奇怪错误处理：
　　下面是导出存储过程的代码
　　1 # mysqldump -u 数据库用户名 -p -n -t -d -R 数据库名 > 文件名
　　其中，-d 表示–no-create-db, -n表示–no-data, -t表示–no-create-info, -R表示导出function和procedure。所以上述代码表示仅仅导出函数和存储过程，不导出表结构和数据。但是，这样导出的内容里，包含了 trigger。再往mysql中导入时就会出问题，错误如下：
 
　　ERROR 1235 (42000) at line **: This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’
　　所以在导出时需要把trigger关闭。代码为
　　1 # mysqldump -u 数据库用户名 -p -n -t -d -R –triggers=false 数据库名 > 文件名
　　这样导入时，会出现新的问题：
　　ErrorCode:1418
　　This function has none of DETERMINISTIC, NOSQL, or READS SQL DATA inits declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
　　解决方法是，在/etc/my.cnf中找到[mysqld],在它下面添加这样一行：
　　1 log-bin-trust-function-creators=1
 
创建数据库：CREATE DATABASE `total_admin` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
1.查看mysql大小
use 数据库名  SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH)  FROM information_schema.TABLES where TABLE_SCHEMA=’数据库名’;
得到的结果是以字节为单位，除1024为K，除1048576为M。
 
2.查看表的最后mysql修改时间select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA=’数据库名’;
安装好mysql的提示：
    
 	
  1. To start mysqld at boot time you have to copy
    2. 
 	
  2. support-files/mysql.server to the right place for your system
    3. 
 	
    4. 
 	
  4. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
    5. 
 	
  5. To do so, start the server, then issue the following commands:
    6. 
 	
    7. 
 	
  7. /usr/bin/mysqladmin -u root password 'new-password'
    8. 
 	
  8. /usr/bin/mysqladmin -u root -h v10 password 'new-password'
    9. 
 	
    10. 
 	
  10. Alternatively you can run:
    11. 
 	
  11. /usr/bin/mysql_secure_installation
    12. 
 	
    13. 
 	
  13. which will also give you the option of removing the test
    14. 
 	
  14. databases and anonymous user created by default.  This is
    15. 
 	
  15. strongly recommended for production servers.
    16. 
 	
    17. 
 	
  17. See the manual for more instructions.
    18. 
 	
    19. 
 	
  19. You can start the MySQL daemon with:
    20. 
 	
  20. cd /usr ; /usr/bin/mysqld_safe &
    21. 
 	
    22. 
 	
  22. You can test the MySQL daemon with mysql-test-run.pl
    23. 
 	
  23. cd /usr/mysql-test ; perl mysql-test-run.pl
    24. 
 	
    25. 
 	
  25. Please report any problems with the /usr/bin/mysqlbug script!
    26.
Comments are closed.