mysql更改数据库编码为utf8

Posted in 2016-12-26 14:56 | Category: Linux | Tags: linux mysql

http://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database

单个修改

数据库

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

数据表

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

数据表字段

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

批量修改

数据库

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like  'database_name';

数据表

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

数据表字段

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns …

开发环境初始化(centos7)

Posted in 2016-12-26 14:56 | Category: Linux | Tags: centos redis linux python mysql

新建用户

[root@localhost]# passwd honmaple
Changing password for user honmaple.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.

安装mysql

参考步骤: http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

下载安装

[root@localhost]# wget http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
[root@localhost]# rpm -Uvh mysql57-community-release-el6-8.noarch.rpm
[root@localhost]# yum list installed | grep mysql
mysql57-community-release.noarch     el6-8                          installed
[root@localhost]# yum install mysql-community-server
错误:软件包:2:postfix-2.10.1-6.el7.x86_64 (@anaconda)
          需要:libmysqlclient.so.18(libmysqlclient_18)(64bit …

mysql部分用户无权限导致无法创建数据库

Posted in 2016-12-25 8:4 | Category: Linux | Tags: linux mysql

记一下

如果记得root用户名及密码

mysql -u root -pyoupassword

如果忘记root用户名及密码

以safe模式启动

mysqld_safe --skip-grant-tables &
mysql -u root

修改user权限

mysql> use mysql;
mysql> show grants;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'youruser'@'%';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

# 这里应该有些问题 maybe set global read_only=OFF/ON;
mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 权限给的太大了
mysql> GRANT ALL PRIVILEGES ON *.* TO 'youruser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec …

pip安装mysql-python出错

Posted in 2016-9-28 11:7 | Category: Python | Tags: pip python mysql

Table of Contents

Collecting mysql-python
  Using cached MySQL-python-1.2.5.zip
    Complete output from command python setup.py egg_info:
    sh: mysql_config: 未找到命令
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-build-g21LDi/mysql-python/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 25, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-g21LDi/mysql-python/

Mac

参考问题

brew install mysql
export PATH=$PATH:/usr/local/mysql/bin
pip install MySQL-Python

Centos

yum install python-devel mysql-devel

Mysql无法启动

Posted in 2016-9-28 10:55 | Category: Linux | Tags: linux mysql

[root@localhost]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@localhost]# systemctl status mysqld
● mysqld.service - SYSV: MySQL database server.
   Loaded: loaded (/etc/rc.d/init.d/mysqld)
   Active: failed (Result: exit-code) since 二 2016-09-13 14:05:27 CST; 16s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 13732 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/FAILURE)

9月 13 14:05:26 localhost.localdomain systemd[1]: Starting SYSV: MySQL datab...
9月 13 14:05:27 localhost.localdomain mysqld[13732]: MySQL Daemon failed to ...
9月 13 14:05:27 localhost.localdomain mysqld[13732]: Starting mysqld:  [FAILED]
9月 13 14:05:27 localhost.localdomain systemd[1]: mysqld.service …