jpuyy的网志

Think before you speak, read before you think.

Category: MySQL

  • mysqldump备份时出现问题

    —

    by

    jpuyy
    in

    使用mysqldump备份时，–opt表示采用优化(Optimize)方式 mysqldump –opt -uroot -ppasswd c14sql >c14sql.sql 出现错误 mysqldump: Got error: 145: Table ‘./c14sql/is_stats’ is marked as crashed and should be repaired when using LOCK TABLES 解决办法1，用myisamchk myisamchk -c -r /var/lib/mysql/c14sql/is_stats.MYI 解决办法2，进入mysql>（暂未测试） mysql> repair table is_stats; 检测一下 mysql> check tables is_stats; 没问题的话再备份就没有问题了 ps: mysqldump 导出表结构 –no-data flag to make it only export structure mysqldump -u…

  • mysql（已知旧密码）修改密码的两种方法

    —

    by

    jpuyy
    in

    http://www.cyberciti.biz/faq/mysql-change-root-password/ 这里的前提是你能登的进mysql 方法一：用mysqladmin命令修改root密码 如果你装mysql的时候用的空密码，现在要设定一个初始密码，那么执行：If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows: $ mysqladmin -u root password NEWPASSWORD 你想要更新密码，用如下命令，However, if you want to change (or…

  • mysql启动失败

    —

    by

    jpuyy
    in

    /usr/sbin/mysqld status 提示 lc-messages-dir=/usr/share/mysql 在http://www.dotdeb.org/2011/11/01/mysql-5-5-is-finally-here/ 给出了解决方法 在my.cnf中注释掉就可以了 “The mysql-common package will be upgraded to version 5.5.17. It’s no big deal, it only contains retro-compatible configuration files.” Wrong. The my.cnf delivered by mysql-common-5.5 contains the lc-messages-dir setting, which MySQL Server 5.1 cannot understand and will fail to start.To everyone who upgraded mysql-common to 5.5, but left…

  • mysql创建用户并控制其对数据库、表、列的权限

    —

    by

    jpuyy
    in

    创建一个用户realabc，步骤为进入mysql库，向user表中插数据，本地登陆，密码abc；flush privileges 使配置生效 mysql> use mysql; mysql> insert into user (Host,User,Password) values (“localhost”,”realabc”,PASSWORD(“abc”)); mysql> flush privileges; 其实直接使用grant命令mysql也会帮我们创建用户 grant all privileges on *.* to ‘yyy’@’localhost’ identified by ‘123’; 接下来想让realabc用户来插入、更新discuzx数据库里的pre_abc、pre_def表，那么肯定是用insert,update,当然还有select，要不数据都没法看到。先查看当前用户的权根： mysql> show grants for realabc@localhost; +—————————————————————————————————————-+ | Grants for realabc@localhost                                                                                   | +—————————————————————————————————————-+ | GRANT USAGE ON *.* TO ‘realabc’@’localhost’ IDENTIFIED BY PASSWORD ‘*0D3CED9BEC10A777AEC23CCC353A8C08A633045E’ | +—————————————————————————————————————-+ 1 row…

  • mysql 命令 | sql语句 | sql语法

    —

    by

    jpuyy
    in

    0、登陆： mysql -u root -p 指定用utf8来连接数据库，大部分时候就不会显示乱码 mysql -u root -p –default-character-set=utf8 1、显示数据库列表。 show databases; 2、显示库中的数据表： use mysql; show tables; 3、显示数据表的结构： describe 表名; 4、建库： create database 库名; create database if not exists 库名 default charset utf8 collate utf8_general_ci; 5、建表： use 库名； create table 表名 (字段设定列表)； 6、删库和删表: drop database 库名; drop table 表名； 7、将表中记录清空： delete from 表名;…