jpuyy的网志

Category: MySQL

  • mysql查找字段值为大写的数据

    ipmi 误写成了 IPMI，现在所有的全查出来

    select * from IPv4Allocation where name= BINARY UPPER(name);

  • mysql binlog参数设定

    mysql使用binlog的好处就是很灵活的进行数据恢复

    binlog设定不对会使磁盘用满报错，出现这种情况首先将数据库里的binlog清理

    根据binlog名进行之前的删除

    PURGE {MASTER | BINARY} LOGS TO 'log_name';

    例：删除mysql-bin.000123之前的binlog

    PURGE MASTER LOGS TO 'mysql-bin.000123';

    根据binlog保留的时间进行删除

    PURGE {MASTER | BINARY} LOGS BEFORE 'date';

    例删除2014-02-19 22:46:26之前的binlog

    PURGE MASTER LOGS BEFORE '2014-02-19 22:46:26';

    删除7天前的binlog

    PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

    故障处理完了，现在要对mysql的binlog保留时间进行调整

    可在my.cnf中设定参数，重启mysql

    expire_logs_days = 7

    不能重启mysql，则需要在mysql命令行中设置，需要flush logs，否则参数不生效过些天又会用满

    mysql> set global expire_logs_days = 7;
mysql> flush logs;

  • sql查询结果保存与文件读取

    将查询结果保存到文件里， 注意 如果已经存在同名文件，则不会创建。

    SELECT…INTO OUTFILE

    例子：

    select name,ctfid,birthday,mobile,tel,email from info where ctfid like '130000%' into outfile '/tmp/fuping-of-rujia';

    同时可以指定分隔符等

    SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM test_table;

    从文件读取的语法为

    LOAD DATA INFILE

    同样可以指定分隔符等，例如从csv文件导入：

    load data local infile '1-200W.csv' into table `info` fields terminated by ',' lines terminated by '\n';

  • mysql将一张表移动到另外的库中

    现在有db1.table1表，现在要将它放到db2中，最简的方法是mysqldump导出，再导入。

    另一种方法是

    create table db2.table2 as select * from db1.table1;

    结构，数据内容一致，但是不会复制索引以及外键

    要保持完全一致，则两步操作，先复制表结构，再复制数据

    create table db2.table2 like db1.table1;
insert into db2.table2 select * from db1.table1;

  • mysql count用法

    其实也是使用了select命令

    查询表中有多少条记录

    select count(*) from table-user;

    上面使用了count(*)会使整张表加载到内存，查询效率较低，毕竟是统计数量，使用count(ID)即可，如

    select count(ID) from table-user;

    用户男女数量统计

    select sex,count(ID) from table-user GROUP BY sex;

  • 制作MariaDB5.5.31的rpm包

    制作MariaDB的rpm包使用CMake CPack Package Generators，这种制作出来的包叫做CPackRPM包。

    首先安装好环境（必须全部检查）：

    bzr > 2.0
    gunzip
    GNU tar
    gcc 2.95.2 or later
    g++
    GNU make 3.75 or later
    libtool 1.5.24 or later
    bison (2.0 for MariaDB 5.5)
    libncurses (在centos下名为ncurses-devel)
    zlib-dev
    GNU automake
    GNU autoconf
    cmake >= 2.8.7 (在centos下名为cmake28)

    使用非root用户，将下载好的mariadb-5.5.31.tar.gz解压，这里是 /home/jpuyy/mariadb-5.5.31。

    将要编译的参数写到cmake后面，这里是最关键的是制作rpm包的参数-DRPM=centos6，语句的最后要有一个点号，表示在当前目录生成。

    cmake28 -DRPM=centos6 \
-DCMAKE_INSTALL_PREFIX=/usr/local/DBServer/MariaDB5.5.31 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=complex .

    当最后几行是如下信息时，表示检查编译通过

    -- Configuring done
-- Generating done
-- Build files have been written to: /home/jpuyy/mariadb-5.5.31

    打包

    make package

    连spec文件都不用写，cmake会自动生成制作rpm的环境，位置在/home/jpuyy/mariadb-5.5.31/_CPack_Packages/Linux/RPM

    最后生成的rpm包如下

    MariaDB-5.5.31-centos6-x86_64-client.rpm
    MariaDB-5.5.31-centos6-x86_64-common.rpm
    MariaDB-5.5.31-centos6-x86_64-devel.rpm
    MariaDB-5.5.31-centos6-x86_64-server.rpm
    MariaDB-5.5.31-centos6-x86_64-shared.rpm
    MariaDB-5.5.31-centos6-x86_64-test.rpm

    这些包的作用

    https://kb.askmonty.org/en/about-the-mariadb-rpm-files/

    安装方法：

    需要解决冲突和依赖

    冲突：查找已经安装的mysql包，rpm -qa ‘mysql*’，移除查找到的mysql包

    rpm -e mysql-libs-5.1.66-2.el6_3.x86_64 --nodeps
rpm -e `rpm -qa mysql-libs*` --nodeps

    依赖：在执行rpm -ivh MariaDB-5.5.31-centos6-x86_64*时，如果出现依赖，如

    error: Failed dependencies:
    perl(DBI) is needed by MariaDB-server-5.5.31-1.x86_64
    perl(Time::HiRes) is needed by MariaDB-test-5.5.31-1.x86_64

    可以yum安装perl-DBI，perl-Time-HiRes。也可以使用

    rpm -ivh MariaDB-5.5.31-centos6-x86_64-* --nodeps

    不去检测依赖。PS:有些依赖确实无用可不用安装。

    参考：

    https://kb.askmonty.org/en/source-building-mariadb-on-centos/

    https://kb.askmonty.org/en/installing-mariadb-with-the-rpm-tool/