龚哥哥 爱生活、做自己!
MySQL mysql-5.1升级到mysql-5.6
发表于 2015-9-1 | 浏览(854) | 数据库

查看是否有旧版本mysql

rpm -qa|grep mysql

CentOS卸载旧版本mysql

yum remove mysql mysql-server mysql-libs compat-mysql51
rm -rf /data/soft/mysql/*
rm /etc/my.cnf.rpmsave

删除mysql服务
  chkconfig --list | grep -i mysql
  chkconfig --del mysql

删除分散mysql文件夹
  whereis mysql 或者 find / -name mysql

安装cmake

wget http://www.cmake.org/files/v2.8/cmake-2.8.10.2.tar.gz
tar -zxvf cmake-2.8.10.2.tar.gz
cd cmake-2.8.10.2
./bootstrap
gmake
gmake install

下载mysql安装包进行安装

wget http://dev.mysql.com/get/archives/mysql-5.6/mysql-5.6.11.tar.gz
tar zxvf mysql-5.6.11.tar.gz
cd mysql-5.6.11
cmake . -DCMAKE_INSTALL_PREFIX=/data/soft/mysql -DMYSQL_DATADIR=/data/dbdata -DSYSCONFDIR=/data/soft/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_USER=mysql -DWITH_DEBUG=0 -DMYSQL_TCP_PORT=3306 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=yes
make
make install

生成配置文件链接

cp /data/mysql/support-files/my-default.cnf /data/mysql/my.cnf
cp /data/mysql/support-files/my-default.cnf /etc/my.cnf

修改$PATH生成文件

修改$PATH生成文件
vim /etc/profile

最后一行加上
PATH=$PATH:/data/soft/mysql/bin
这样MySQL的命令行软件就可以直接使用了

复制mysql的服务shell文件

cp /data/soft/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld

创建MySQL系统数据库

/data/soft/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/soft/mysql --datadir=/data/dbdata

开启MySQL服务

service mysqld start

设置MySQL开机启动

echo "service mysqld start" >> /etc/rc.local

到这里mysql安装完成了, 我们开始使用客户端连接mysql

如果报错:
  SQL Error (1130): Host 'ip' is not allowed to connect to this MySQL server

首先按下面的步骤登录Mysql服务器

登录mysql需要切换到dos下的mysql的bin目录,进行如下操作:

#mysql -uroot -ppassword
mysql>use mysql;

mysql>update user set host = '%'  where user ='root';

mysql>flush privileges;

mysql>select 'host','user' from user where user='root';

mysql>quit
OK。远程连接成功!

阅读全文

MySQL查询重复出现次数最多的记录
发表于 2015-9-1 | 浏览(757) | 数据库

单表查询

SELECT DISTINCT COUNT(*) AS count, lid FROM user_label GROUP BY lid ORDER BY count DESC LIMIT 0, 10;

联表查询user_label 用户标签关联表(uid, lid), label 标签表(lid, name)

SELECT DISTINCT count(u.lid) AS count, u.lid, l.name FROM `label` AS l INNER JOIN `user_label` AS u ON u.lid=l.lid GROUP BY u.lid ORDER BY count DESC LIMIT 0, 10

阅读全文

查看MySQL SQL语句执行的时间
发表于 2015-9-1 | 浏览(716) | 数据库

1;在控制台操作,查看是否已开启profile

show variables like "%pro%";

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| have_profiling            | YES   |
| profiling                 | OFF   |未开启
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| proxy_user                |       |
| slave_compressed_protocol | OFF   |
| stored_program_cache      | 256   |
+---------------------------+-------+

2;开启profile

set profiling=1;
show variables like "%pro%";

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| have_profiling            | YES   |
| profiling                 | ON    |
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| proxy_user                |       |
| slave_compressed_protocol | OFF   |
| stored_program_cache      | 256   |
+---------------------------+-------+

3;进行测试(以毫秒为单位)

select * from user;
show profiles;

+----------+------------+-----------------------------+
| Query_ID | Duration| Query                       |
+----------+------------+-----------------------------+
| 1        | 0.00152800 | show variables like "%pro%" |
| 2        | 0.00098075 | show tables                 |
| 3        | 0.00035975 | select * from user          |
| 4        | 0.00077625 | select * from user      |
| 5        | 0.00090725 | show variables like "%pro%" |
| 6        | 0.00068650 | select * from user      |
| 7        | 0.00069100 | select * from user      |
| 8        | 0.00088225 | select * from user      |
+----------+------------+-----------------------------+

4;查看指定语句的详细执行(Query_ID)

show profile for query 8;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000084 |
| checking permissions | 0.000016 |
| Opening tables       | 0.000038 |
| init                 | 0.000068 |
| System lock          | 0.000020 |
| optimizing           | 0.000011 |
| statistics           | 0.000032 |
| preparing            | 0.000019 |
| executing            | 0.000005 |
| Sending data         | 0.000347 |
| end                  | 0.000014 |
| query end            | 0.000008 |
| closing tables       | 0.000054 |
| freeing items        | 0.000141 |
| cleaning up          | 0.000027 |
+----------------------+----------+

阅读全文

ubuntu下MySQL停止或启动失败
发表于 2015-8-31 | 浏览(622) | 服务器

有些时候遇到一些奇怪的问题就是mysql不管是启动还是停止都失败。

在这里先看看进程

ps -ef | grep mysql

根据进程号直接杀死进程

kill 27362 29144 29173

然后再停止或启动操作即可

/etc/init.d/mysql stop                停止
/etc/init.d/mysql start               启动
/etc/init.d/mysql restart             重启

阅读全文

PHP localhost连接不上MySQL
发表于 2015-8-31 | 浏览(770) | PHP

连接MySQL数据库有两种方式:TCP/IP(一般理解的端口的那种)和Unix套接字(一般叫socket或者sock)。大部分情况下,可以用localhost代表本机127.0.0.1,但是在MySQL连接时,二者不可混用,而且MySQL中权限设置中localhost与127.0.0.1也是分开设置的。当设置为127.0.0.1时,系统通过TCP/IP方式连接数据库;当设置为localhost时,系统通过socket方式连接数据库。

找到mysql.sock所在位置,常在目录 tmp , var/run/mysqld

修改 php.ini

pdo_mysql.default_socket=/tmp/mysql.sock
mysql.default_socket = /tmp/mysql.sock
mysqli.default_socket = /tmp/mysql.sock

mac重启apache
linux重启php-fpm

阅读全文

TOP