龚哥哥 - 山里男儿 爱生活、做自己!
CentOS编译安装MySQL5.7
发表于 2016-10-12 | 浏览(9286) | 数据库

1、准备

mkdir /data
cd /data
mkdir soft src dbdata
cd soft
mkdir mysql

2、安装cmake

cd /data/src
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

3、下载boost源码包,从5.7开始boost包是必须的

cd /data/src
wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
mkdir /usr/local/boost
tar -zxvf boost_1_59_0.tar.gz -C /usr/local/boost

4、安装mysql

cd /data/src
wget http://dev.mysql.com/get/archives/mysql-5.7/mysql-5.7.15.tar.gz
tar -zxvf mysql-5.7.15.tar.gz
cd mysql-5.7.15
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 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
make
make install

5、初始化操作

cd /data/soft/mysql
bin/mysqld --initialize --user=mysql --basedir=/data/soft/mysql --datadir=/dbdata --explicit_defaults_for_timestamp=1
/data/soft/mysql/support-files
这里会给出默认的随机密码,请一定要记下来一会儿登录使用(mysql从5.7开始密码不再默认空了)

6、mysql配置文件

cd support-files
cp my-default.cnf /etc/my.cnf

7、配置mysql服务开机自动启动

cp mysql.server /etc/init.d/mysql
chmod 755 /etc/init.d/mysql

8、检查自启动项列表中没有mysql这个,如果没有就添加mysql

chkconfig --list mysql
chkconfig --add mysql

9、设置MySQL在345等级自动启动

chkconfig --level 345 mysql on
chkconfig mysql on

10、创建mysql命令路径

ln -s /data/soft/mysql/bin/mysql /usr/bin

11、mysql操作命令

service mysql start
service mysql stop
service mysql restart

12、登录mysql

mysql -uroot -p
输入安装完成的时候给的随机密码,如:123456

13、修改root的密码

alter user 'root'@'localhost' identified by '123456';

退出后重新登录使用新密码即可

阅读全文

MySQL中MyISAM引擎表级锁
发表于 2016-7-6 | 浏览(6914) | 数据库

表名:lock_read

表类型:MyISAM(此引擎只支持表级锁,不支持行级锁,下面是测试表级锁)

锁类型:表锁

表结构:

CREATE TABLE `lock_read` (
    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` char(30) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` tinyint(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '年龄',
    PRIMARY KEY (`id`)
) ENGINE=`MyISAM` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=FIXED COMMENT='测试表级锁' CHECKSUM=0 DELAY_KEY_WRITE=0;

添加一条数据:INSERT INTO `lock_read` (`name`, `age`) VALUES ('小龚', 22);

终端一

1、查询本身数据
  SELECT * FROM lock_read;
  +----+--------+-----+
  | id | name   | age |
  +----+--------+-----+
  | 1  | 小龚   | 22  |
  +----+--------+-----+

2、加表锁
  LOCK TABLES lock_read READ;

3、更新数据
  UPDATE lock_read SET age=23 WHERE id=1;
  报错:Table 'lock_read' was locked with a READ lock and can't be updated
  解释:意思是表当前加锁中,不能被任何人更新(包括当前进程)

终端二

1、查询本身数据
  SELECT * FROM lock_read;
  +----+--------+-----+
  | id | name   | age |
  +----+--------+-----+
  | 1  | 小龚   | 22  |
  +----+--------+-----+

2、加表锁
  LOCK TABLES lock_read READ;

3、更新数据
  UPDATE lock_read SET age=24 WHERE id=1;
  报错:Table 'lock_read' was locked with a READ lock and can't be updated
  解释:意思是表当前加锁中,不能被任何人更新(包括当前进程)

  但是如果当前进程跳过步骤2,不对表进行加锁,那么是这样子的:
    前面进程已加锁了,那么当前进程就会进入阻塞状态,等到终端一的进程释放锁后,这条sql才能被执行成功

终端一

1、解锁
  UNLOCK TABLES;

2、更新数据
  UPDATE lock_read SET age=25 WHERE id=1;
  A、如果终端二加锁过了,那么当前进程就会进入阻塞状态,等待终端二的进程解锁后,这条sql才能被只成功
  B、如果终端二没有加锁,那么当前sql将执行成功

阅读全文

MySQL默认编码修改
发表于 2016-6-30 | 浏览(5820) | 数据库

mac下my.cnf默认位置

/usr/local/mysql/support-files
拷贝一份到/etc目录下 cp my-default.cnf /etc/my.cnf

mysql默认编码并不是utf-8,而是latin1(不支持中文,存储中文乱码)登录后使用 show variables 可以查看

1、vim /etc/my.cnf
  在 [mysqld] 下面添加默认编码
  character_set_server=utf8 
  init_connect='SET NAMES utf8'

2、重启mysql即可

阅读全文

MySQL SELECT语句IN与OR的效率
发表于 2016-6-29 | 浏览(7094) | 数据库

MySQL版本 5.7.13、SELECT语句IN与OR的效率比较,以下是数据查询量,表总数据量10万条。

100条数据
  字段为主键的情况
    in:0.04 sec
    or:0.06 sec

  添加索引的情况
    in:0.00 sec
    or:0.01 sec

  无索引的情况
    in:0.04 sec
    or:0.06 sec

1万条数据
  字段为主键的情况
    in:0.06 sec
    or:2 min 37.90 sec

  添加索引的情况
    in:0.08 sec
    or:2 min 39.22 sec

  无索引的情况
    in:0.07 sec
    or:2 min 39.73 sec

 10万条数据
  字段为主键的情况
    in:2.74 sec
    or:13 min 46.52 sec

  添加索引的情况
    in:2.94 sec
    or:14 min 13.72 sec

  无索引的情况
    in:4.35 sec
    or:15 min 19.39 sec

查询数据小的时候并无多大差异,随着查询数据量增大OR就显的越来越慢。

创建表

CREATE TABLE `test_in_or` (
    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `number` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '编号',
    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    PRIMARY KEY (`id`)
) ENGINE=`InnoDB` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=DYNAMIC COMMENT='测试or和in的效率' CHECKSUM=0 DELAY_KEY_WRITE=0;

添加索引

添加普通索引
  ALTER TABLE `test_in_or` ADD INDEX number(`number`);

删除普通索引
  ALTER TABLE `test_in_or` DROP INDEX `number`;

添加主键索引
  添加(PS在这里不需要):ALTER TABLE `test_in_or` ADD PRIMARY KEY (`number`);

  更新主键:ALTER TABLE `test_in_or` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `number`);

PHP生成代码(语句太长,贴出来不容易)

数据写入sql语句
  $sql = 'INSERT INTO test_in_or (`number`) VALUES';
  $number = 100000;
  for($i=1; $i<=$number; $i++)
  {
    $sql .= ' ('.$i.'),';
  }
  echo substr($sql, 0, -1).';';

SELECT IN语句
  $number = 100000;
  $sql = 'SELECT * FROM test_in_or WHERE `number` IN (';
  for($i=1; $i<=$number; $i++)
  {
    $sql .= $i.', ';
  }
  echo substr($sql, 0, -2).');';

SELECT OR语句
  $number = 100000;
  $sql = 'SELECT * FROM test_in_or WHERE';
  for($i=1; $i<=$number; $i++)
  {
    $sql .= ' `number`='.$i.' OR';
  }
  echo substr($sql, 0, -3).';';

阅读全文

mac 10.11.5 安装 mysql-5.7.13
发表于 2016-6-24 | 浏览(6161) | 数据库

下载地址

http://dev.mysql.com/downloads/mysql/
  Mac OS X 10.11 (x86, 64-bit), DMG Archive

5.7以后的mysql不再使用旧版本的默认密码 root,安装过程中会出现弹出一个提示,那里是一个临时密码,请复制出来保存,以后会需要用到它。如下:

2016-06-25T18:16:46.203256Z 1 [Note] A temporary password is generated for root@localhost: kF-dx_4aycye
If you lose this password, please consult the section How to Reset the Root Password in the MySQL reference manual.

进入终端 /usr/local/mysql/bin/目录

mysqladmin -u root -p password
输入临时密码 如上密码是:kF-dx_4aycye

然后输入你临时密码,成功以后会

New password:  
Confirm new password:
让你输入新密码

紧接着出现Since password will be sent to server in plain text, use ssl connection to ensure password safety.这只是一个提示,可以无视。

到这里密码设置完成,可以使用命令行或者第三方管理软件登录mysql了

阅读全文

MySQL常用操作
发表于 2015-12-4 | 浏览(6513) | 数据库
查看正在执行的sql语句
    show processlist;

停止正在执行的sql语句
    kill id(正在执行的sqlID);

查询的时候增加一个字符串一起返回
    SELECT CONCAT("strvalue") AS str table;

表修复
    myisamchk -r table

获取表字段名称和注释
    SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.Columns WHERE table_name='table_name';

查询表结构, 所有字段和属性
    SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='table_name' 

中文按照首字母排序
    SELECT * FROM table ORDER BY CONVERT(nickname USING gbk) asc;

整数值、表结构字符串类型 转为整数进行排序
    SELECT * FROM table ORDER BY CONVERT(code, SIGNED) asc;

中英文混合排序

写一个方法获取第一个字符,中文则转为字母,字母或数字则截取第一个字母返回

# 方法
CREATE FUNCTION `MY_FIRST_PINYIN`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    COMMENT '中文首字母转拼音'
BEGIN
    DECLARE V_RETURN VARCHAR(255);
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10), 
        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7, 
        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
        0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),    
    'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');

    IF ISNULL(V_RETURN) THEN
        SET V_RETURN = LEFT(P_NAME,1);
    END IF;
    RETURN UPPER(V_RETURN);
END;

# 查询语句
SELECT * FROM table ORDER BY MY_FIRST_PINYIN(nickname) asc;

阅读全文

MySQL双主_单主_热备份_实时备份
发表于 2015-8-31 | 浏览(6467) | 数据库

6630381670443118394.jpg

两台服务器

主库ip:192.168.1.1
从库ip:192.168.1.2

主库配置、编辑 my.cnf  [mysqld]下添加

server-id=1
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
replicate-do-db=devil #需要备份的数据库
slave_skip_errors = 1062 #忽略错误

#解决互相数据插入自增冲突
auto_increment_offset=1
auto_increment_increment=2

保存重启mysql

从库配置、编辑 my.cnf  [mysqld]下添加

server-id=2
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
replicate-do-db=devil #需要备份的数据库
slave_skip_errors = 1062 #忽略错误

#解决自增冲突
auto_increment_offset=2
auto_increment_increment=2

保存重启mysql

主库配置、进入mysql控制台执行

mysql -uroot -proot

stop slave;

change master to master_host='192.168.1.2',master_user='root',master_password='root';

开启配置
start slave;

查看警告
Show warnings;

查看配置状态
show slave status\G

    以下两项yes表示状态配置成功,如果error有错误根据提示解决:
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

从库配置、进入mysql控制台执行

change master to master_host='192.168.1.1',master_user='root',master_password='root';

stop slave;

start slave;

Show warnings;

show slave status\G

以下两项yes表示状态配置成功,如果error有错误根据提示解决:
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

如果以上顺利通过,则可以进行测试了。以下是可能遇到的问题。

日志冲突解决

查看所有二进制日志
SHOW MASTER LOGS;

清空所有二进制日志
RESET MASTER;

网络连接失败

Last_IO_Errno: 1130
1;一般是防火墙开启,拒绝3306端口。或者是配置错误,如用户名或密码
2;还有可能是主库备份帐号的访问权限,进入主库mysql控制台
  use mysql;
  select * from user where user='root';
  update user set host = '%' where user ='root';
  flush privileges;

如果不使用root用户,可以创建一个备份用户

grant replication slave,replication client on *.* to '用户名'@'可执行操作的ip' identified by '密码';

阅读全文

CentOS下完全卸载mysql
发表于 2015-8-31 | 浏览(5598) | 数据库

一、yum方式安装的mysql

yum remove mysql mysql-server mysql-libs compat-mysql51
rm -rf /var/lib/mysql
rm /etc/my.cnf

查看是否还有mysql软件

rpm -qa|grep mysql

如果存在的话,继续删除即可。

二、rpm方式安装的mysql

1、查看系统中是否以rpm包安装的mysql:

[root@localhost opt]# rpm -qa | grep -i mysql
MySQL-server-5.6.17-1.el6.i686
MySQL-client-5.6.17-1.el6.i686

2、卸载mysql

[root@localhost local]# rpm -e MySQL-server-5.6.17-1.el6.i686
[root@localhost local]# rpm -e MySQL-client-5.6.17-1.el6.i686

3、删除mysql服务

[root@localhost local]# chkconfig --list | grep -i mysql
[root@localhost local]# chkconfig --del mysql

4、删除分散mysql文件夹

[root@localhost local]# whereis mysql 或者 find / -name mysql
mysql: /usr/lib/mysql /usr/share/mysql

清空相关mysql的所有目录以及文件

rm -rf /usr/lib/mysql
rm -rf /usr/share/mysql
rm -rf /usr/my.cnf

通过以上几步,mysql应该已经完全卸载干净了

阅读全文

MySQL mysql-5.1升级到mysql-5.6
发表于 2015-8-31 | 浏览(5542) | 数据库

查看是否有旧版本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-8-31 | 浏览(6160) | 数据库

单表查询

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

阅读全文

TOP