### 一，分库 ####1.1 按照功能分库 按照功能进行分库。常见的分成6大库： 1 用户类库：用于保存了用户的相关信息。例如：db_user,db_system,db_company等。 2 业务类库：用于保存主要业务的信息。比如主要业务是笑话，用这个库保存笑话业务。例如：db_joke,db_temp_joke等。 3 内存类库：主要用Mysql的内存引擎。前台的数据从内存库中查找，速度快。例如：heap。 4 图片类库：主要保存图片的索引以及关联。例如：db_img_index，db_img_res。 5 日志类库：记录点击,刷新,登录等日志信息。例如：db_log_click,db_log_fresh,db_log_login。 6 统计类库：对业务的统计，比如点击量,刷新量等等。例如db_stat。      #### 1.2 安装城市站分库 如果业务遍布全国，在按照功能分库库,每一个城市复制一份一模一样的库,只是库后缀都是城市名称。比如db_log_click_bj,db_log_click_tj,db_log_click_sh；      ### 二，分表 #### 2.1 水平分割。解决表行数过大问题 2.1.1 按照用户或业务的编号分表 对与用户或业务可以按照编号%n，进行分成n表。 例如：笑话表。 tb_joke_01,tb_joke_02,tb_joke_03,tb_joke_04........   #### 2.1.2 按照日期分表 对于日志或统计类等的表。可以按照年,月，日，周分表。 例如 点击量统计。 tb_click_stat_201601，tb_click_stat_201602，tb_click_stat_201603 #### 2.2 垂直分割。解决列过长问题。 1）经常组合查询的列放在一张表中。常用字段的表可以考虑用Memory引擎。 2）把不常用的字段单独放在一张表。 3）把text，blob等大字段拆分出来放在附表中。 ### 三，Mysql数据库常用架构 核心：一主多从，读写分离。 

使用方法 ~~~ mysql>show table status; mysql>show table status like 'esf_seller_history'\G; mysql>show table status like 'esf_%'\G; ~~~ 样例： `mysql>show table status like 'esf_seller_history'\G;`  1.Name          表名称 2.Engine:        表的存储引擎 3.Version:        版本 4.Row_format        行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。 5\. Rows        表中的行数。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。 6.Avg_row_length        平均每行包括的字节数  7.Data_length        整个表的数据量(单位：字节) 8.Max_data_length       表可以容纳的最大数据量 9.Index_length       索引占用磁盘的空间大小  10.Data_free      对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间。 11.Auto_increment      下一个Auto_increment的值 12.Create_time      表的创建时间 13.Update_time      表的最近更新时间 14.Check_time      使用 check table 或myisamchk工具检查表的最近时间 15.Collation      表的默认字符集和字符排序规则 16.Checksum 如果启用，则对整个表的内容计算时的校验和 17.Create_options 指表创建时的其他所有选项 18.Comment 包含了其他额外信息，对于MyISAM引擎，包含了注释徐标新，如果表使用的是innodb引擎 ，将现实表的剩余空间。如果是一个视图，注释里面包含了VIEW字样。

### 一，mysql整型类型的存储字节与范围。 CleverCode最近研究了一下mysql的整数类型。合理的利用整形的类型，可以大大提高msyql的效率。建议最好不要只要是整数，都设计为int。mysql支持：tinyint，smallint，mediumint，int，bigint。根据自己的需求合理的选择整形类型。可以节约msyql的磁盘空间。如下图  ### 二，2 mysql最大长度默认值。 tinyint 默认4，smallint 默认6，mediumint 默认9，int 默认11，bigint 默认20，存储的默认最大长度如图。  ### 三，mysql的存储 如果设计表是int(3)。虽然设置了最大长度是3，但是数据存储还是占用了4个字节。 ~~~ mysql> create table table_test(`a` int(3) default NULL); ~~~ 当数据长度小与3位时候的存储,实际上存储的是020。前面用0补充达到长度3位。但是 获取出来数据的时候会把补充的0给去掉。 ~~~ mysql> insert into table_test(a) values(2); ~~~ 当数据长度大于3位时候的存储,将会突破3个的限制。 ~~~ mysql> insert into table_test(a) values(2000); ~~~

CleverCode发现一个导出mysql数据成csv格式的简单方法。 ### 1）mysql -e使用简介 使用说明：执行一个sql语句并且退出。 格式样例： ~~~ 1)mysql -uroot -p123456 -h 192.168.10.210 -P 3306 db_logs -e "show databases;" 2)mysql -uroot -p123456 -h 192.168.10.210 -P 3306 db_logs -e "select cdate,uid,clientip,createtime from user_login_log limit 1;"  ~~~ 说明：     -u:root为用户名；     -p:123456为密码     -h:mysql服务器ip     -P:3306mysql服务器端口     db_logs:执行语句所在的数据库     -e：需要执行的语句。 ### 2）直接执行语句 ~~~  # mysql -uroot -p123456 -h 192.168.10.210 -P 3306 db_logs -e "select cdate,uid,clientip,createtime from user_login_log limit 1;"   ~~~    ### 3）保存执行语句到文件 ~~~  # mysql -uroot -p123456 -h 192.168.10.210 -P 3306 db_logs -e "select cdate,uid,clientip,createtime from user_login_log limit 1;"  > log.txt  # cat log.txt  ~~~ 可以看到保存的结果是\t分隔的。  ### 4）保存执行语句到文件，使用awk替换\t成逗号 ~~~  # mysql -uroot -p123456 -h 192.168.10.210 -P 3306 db_logs -e "select cdate,uid,clientip,createtime from user_login_log limit 1;" | awk '{print $1",",$2","$3","$4}' > log.csv  # cat log.csv ~~~  

### 一，有问题的代码 CleverCode发现在mysql有分页丢失数据的问题。如下面的代码，system_user表有400万行数据，这时候需要把这张表里面的所有的userid取出来。这时候只能分页取，一次取2万。 ~~~ //系统用户表(400万行的数据) $systemUserTable = new systemUserTable(); // 每次取20000 $pageSize = 20000; for($curPage = 1;; $curPage++) { $sql = "select userid from system_user order by userid asc limit " . (($curPage - 1) * $pageSize) . "," . $pageSize; //取数据 $rows = $systemUserTable->fetchAll($sql); // 没有数据了，结束 if (empty($rows)) { break; } // 遍历数据 foreach ( $rows as $key => $val ) { $userid = $val['userid']; echo $userid."\r\n"; } } ~~~ ### 二，问题分析 1）假如system_user只有10行数据(u1,u2,u3,u4,u5,u6,u7,u8,u9,u10)。 2) 这时候每次取5行。分2两次取。 3）正常情况第一页取(u1,u2,u3,u4,u5)，正常情况第二页取(u6,u7,u8,u9,u10)。 4）如果在第一页去完，第二页取之前的时间间隙里面。u2,u3被删除了。 5）那么第二页取的时候。数据变成了(u1,u4,u5,u6,u7,u8,u9,u10)。 6）那么取出来第二页的数据就变成了（u8,u9,u10）。 7）u6,u7被丢失了。 ### 三，解决方案 每次取的时候where带着最大偏移量。如下面的代码。 ~~~ $systemUserTable = new systemUserTable(); // 每次取20000 $pageSize = 20000; // 每页最大编号 $pageMaxId = 0; while ( 1 ) { $sql = "select userid from system_user where userid > ${pageMaxId} order by userid asc limit " . $pageSize; //取数据 $rows = $fyHouse->fetchAll($sql); // 没有数据，结束 if (empty($rows)) { break; } // 遍历数据 foreach ( $rows as $key => $val ) { $userid = $val['userid']; // 记录当前最大分页编号 if ($userid > $pageMaxId) { $pageMaxId = $userid; } echo $userid."\r\n"; } } ~~~ 过程分析： 1）system_user只有10行数据(u1,u2,u3,u4,u5,u6,u7,u8,u9,u10)。 2) 第一次取，最大偏移量0,limit 5;则取出(u1,u2,u3,u4,u5)。记录最大的偏移量u5的值。 4）如果在第一页去完，第二页取之前的时间间隙里面。u2,u3被删除了。 5）那么第二页取的时候。数据变成了(u1,u4,u5,u6,u7,u8,u9,u10)。 3）第二次取，最大偏移量u5,limit 5;则取出(u6,u7,u8,u9,u10)。记录最大的偏移量u10的值。 

CleverCode最近在研究mysql的多实例，发现有两种方式： 第一种是使用多个配置文件启动不同的进程来实现多实例。这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。 第二种是通过官方自带的mysqld_multi。使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。 推荐使用多个配置文件方式。这种实际应用中好，耦合性不强，配置方便，特别是主从复制的时候。 上一篇《mysql多实例（多个配置文件方式）》：http://blog.csdn.net/clevercode/article/details/47610619。介绍了多个配置文件方式。本篇将介绍mysqld_multi方式。 1 环境介绍： 1）简介 mysql 版本：mysql-5.5.27 cmake：cmake-2.8.8 操作系统：CentOS6.5 mysql实例数：3个 实例占用端口分别为：3306、3307、3308 2）本次安装所有的软件资源包下载地址 http://download.csdn.net/detail/clevercode/8662323 2 配置防火墙 1) 在防火墙配置文件中添加3306，3307,3308（允许3306，3307,3308端口通过防火墙）  ~~~  # vi /etc/sysconfig/iptables   #编辑防火墙配置文件  -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3308 -j ACCEPT ~~~ 2）重启防火墙 ~~~  # /etc/init.d/iptables restart  #最后重启防火墙使配置生效   ~~~ 3 安装cmake 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf cmake-2.8.8.tar.gz  # cd cmake-2.8.8 ~~~ 配置 ` # ./configure`   编译 ` # make`   安装 ` # make install `   查看版本 ` # cmake -version`   4 安装Mysql 1) 创建用户 ~~~  # groupadd mysql  #添加mysql组      # useradd -g mysql mysql -s /bin/false  #创建用户mysql并加入到mysql组，不允许mysql用户直接登录系统 ~~~ 2) 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf mysql-5.5.27.tar.gz  # cd mysql-5.5.27 ~~~ 4) 配置 ` # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql `   PS: cmake的时候，参数可以不用那么多，只要一个-DCMAKE_INSTALL_PREFIX=/usr/local/mysql就行了，我们可以在 my.cnf里面配置。[mysqld]中的内容，看看你copy后的my.cnf有没有这些设置，有就不用了在设置了。   5）编译 ` # make `   6)安装 ` # make install ` 5 初始化数据库 ~~~  # mkdir -p /data0/dbdata/mysql/3306  # mkdir -p /data0/dbdata/mysql/3307  # mkdir -p /data0/dbdata/mysql/3308  # chown -R mysql:mysql /data0/dbdata/mysql/3306  # chown -R mysql:mysql /data0/dbdata/mysql/3307  # chown -R mysql:mysql /data0/dbdata/mysql/3308  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3306 --user=mysql  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3307 --user=mysql  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3308 --user=mysql ~~~ 6 修改配置文件（只用/etc/my.cnf文件） ~~~  # cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf  # vi /etc/my.cnf ~~~ ~~~ [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin  #用于登陆和关闭此服务 user = root password = CleverCode123 [mysqld3306]  #数据目录 datadir = /data0/dbdata/mysql/3306  #连接 port = 3306 socket = /tmp/mysql3306.sock  #binlog log-bin=/data0/dbdata/mysql/3306/mysql-bin pid-file = /data0/dbdata/mysql/3306/mysql_3306.pid [mysqld3307] datadir = /data0/dbdata/mysql/3307 port = 3307 socket = /tmp/mysql3307.sock log-bin=/data0/dbdata/mysql/3307/mysql-bin pid-file = /data0/dbdata/mysql/3307/mysql_3307.pid [mysqld3308] datadir = /data0/dbdata/mysql/3308 port = 3308 socket = /tmp/mysql3308.sock log-bin=/data0/dbdata/mysql/3308/mysql-bin pid-file = /data0/dbdata/mysql/3308/mysql_3308.pid ~~~ 7 启动3306、3307、3308的mysql  ~~~  # /usr/local/mysql/bin/mysqld_multi start 3306  # /usr/local/mysql/bin/mysqld_multi start 3307  # /usr/local/mysql/bin/mysqld_multi start 3308 ~~~ 8 查看端口是否监听，如果出现3306,3307,3308则启动正常 ~~~  # netstat -anp | grep 3308 tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      2348/mysqld          unix  2      [ ACC ]     STREAM     LISTENING     10780  2348/mysqld         /tmp/mysql3308.sock   ~~~ 9 初始化密码并且授权远程登录，mysqladmin用户名和密码需要和/ect/my.cnf中保持一致，否则stop不了服务。 ~~~  # /usr/local/mysql/bin/mysqladmin -u root password "CleverCode123" -S /tmp/mysql3306.sock  # /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3306.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3306' with grant option; mysql> flush privileges;  # /usr/local/mysql/bin/mysqladmin -u root password "CleverCode123" -S /tmp/mysql3307.sock  # /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3307.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3307' with grant option; mysql> flush privileges;  # /usr/local/mysql/bin/mysqladmin -u root password "CleverCode123" -S /tmp/mysql3308.sock  # /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3308.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3308' with grant option; mysql> flush privileges; ~~~ 10 停止（必须先初始化密码）。可以使用netstat -anp | grep 3308查看监听进程是否还存在 ~~~  # /usr/local/mysql/bin/mysqld_multi stop 3306  # /usr/local/mysql/bin/mysqld_multi stop 3307  # /usr/local/mysql/bin/mysqld_multi stop 3308 ~~~ 11 报告。显示进程的状态 ~~~  # /usr/local/mysql/bin/mysqld_multi report 3306  # /usr/local/mysql/bin/mysqld_multi report 3307  # /usr/local/mysql/bin/mysqld_multi report 3308 ~~~

CleverCode最近在研究mysql的多实例，发现有两种方式： 第一种是使用多个配置文件启动不同的进程来实现多实例。这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。 第二种是通过官方自带的mysqld_multi。使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。 推荐使用多个配置文件方式。这种实际应用中好，耦合性不强，配置方便，特别是主从复制的时候。 这里分两次介绍，先介绍多个配置文件方式。 1 环境介绍： 1）简介 mysql 版本：mysql-5.5.27 cmake：cmake-2.8.8 操作系统：CentOS6.5 mysql实例数：3个 实例占用端口分别为：3306、3307、3308 2）本次安装所有的软件资源包下载地址 http://download.csdn.net/detail/clevercode/8662323 2 配置防火墙 1) 在防火墙配置文件中添加3306，3307,3308（允许3306，3307,3308端口通过防火墙）  ~~~  # vi /etc/sysconfig/iptables   #编辑防火墙配置文件  -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3308 -j ACCEPT ~~~ 2）重启防火墙 ` # /etc/init.d/iptables restart  #最后重启防火墙使配置生效 ` 3 安装cmake 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf cmake-2.8.8.tar.gz  # cd cmake-2.8.8 ~~~ 配置 ` # ./configure ` 编译 ` # make ` 安装 ` # make install ` 查看版本 ` # cmake -version ` 4 安装Mysql 1) 创建用户 ~~~  # groupadd mysql  #添加mysql组      # useradd -g mysql mysql -s /bin/false  #创建用户mysql并加入到mysql组，不允许mysql用户直接登录系统 ~~~ 2) 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf mysql-5.5.27.tar.gz  # cd mysql-5.5.27 ~~~ 4) 配置 ` # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  ` PS: cmake的时候，参数可以不用那么多，只要一个-DCMAKE_INSTALL_PREFIX=/usr/local/mysql就行了，我们可以在 my.cnf里面配置。[mysqld]中的内容，看看你copy后的my.cnf有没有这些设置，有就不用了在设置了。   5）编译 ` # make ` 6)安装 ` # make install ` 5 初始化数据库 ~~~  # mkdir -p /data0/dbdata/mysql/3306  # mkdir -p /data0/dbdata/mysql/3307  # mkdir -p /data0/dbdata/mysql/3308  # chown -R mysql:mysql /data0/dbdata/mysql/3306  # chown -R mysql:mysql /data0/dbdata/mysql/3307  # chown -R mysql:mysql /data0/dbdata/mysql/3308  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3306 --user=mysql  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3307 --user=mysql  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3308 --user=mysql ~~~ 6 修改配置文件 ~~~  # cp /usr/local/mysql/support-files/my-huge.cnf /data0/dbdata/mysql/3306/my.cnf  # cp /usr/local/mysql/support-files/my-huge.cnf /data0/dbdata/mysql/3307/my.cnf  # cp /usr/local/mysql/support-files/my-huge.cnf /data0/dbdata/mysql/3308/my.cnf  # vi /data0/dbdata/mysql/3308/my.cnf    #编辑配置文件（3306,3307同理）  #在 [client]部分修改一下数据 port= 3308 socket= /tmp/mysql3308.sock  #在 [mysqld] 部分修改数据 port= 3308 socket= /tmp/mysql3308.sock datadir = /data0/dbdata/mysql/3308  #添加MySQL数据库路径  :wq!  #保存退出  ~~~ 7 创建自动启动文件  ~~~  # cp /usr/local/mysql/support-files/mysql.server  /etc/rc.d/init.d/mysqld3306  # cp /usr/local/mysql/support-files/mysql.server  /etc/rc.d/init.d/mysqld3307  # cp /usr/local/mysql/support-files/mysql.server  /etc/rc.d/init.d/mysqld3308  # chmod 755 /etc/rc.d/init.d/mysqld3306  # chmod 755 /etc/rc.d/init.d/mysqld3307  # chmod 755 /etc/rc.d/init.d/mysqld3308  # vi /etc/rc.d/init.d/mysqld3308  #编辑 （3306,3307同理）    basedir=/usr/local/mysql   #MySQL程序安装路径     datadir=/data0/dbdata/mysql/3308  #MySQl数据库存放目录  ~~~ 修改 ~~~ $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 & ~~~ 变成 ~~~ $bindir/mysqld_safe --defaults-file="$datadir/my.cnf" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 & ~~~ 8 启动3306、3307、3308的mysql  ~~~  # service mysqld3306 restart  # service mysqld3307 restart  # service mysqld3308 restart ~~~ 9 查看端口是否监听，如果出现3306,3307,3308则启动正常 ~~~  # netstat -anp | grep 3308 tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      2348/mysqld          unix  2      [ ACC ]     STREAM     LISTENING     10780  2348/mysqld         /tmp/mysql3308.sock   ~~~ 10 初始化密码并且授权远程登录（3306,3307同理） ~~~  # /usr/local/mysql/bin/mysqladmin -u root password "pwd3308" -S /tmp/mysql3308.sock  # /usr/local/mysql/bin/mysql -uroot -ppwd3308 -S  /tmp/mysql3308.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3308' with grant option; mysql> flush privileges; ~~~

主从复制原理： 1 复制架构图  2 初始化图  3 复制原理 mysql使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上）。当发出start slave时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以即为主服务器上show processlist输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是sql线程，由从服务器创建，用于读取中继日志并执行日志中包含的更新。在从服务器上，读取和执行更新语句被分成两个独立的任务。当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容。 1、主从服务器分别作以下操作：   1.1、版本一致。（mysql-5.5.27）   1.2、初始化表，并在后台启动mysql   1.3、修改root的密码   1.4、主服务器master:192.168.142.131   1.4、从服务器slave:192.168.142.133 2、修改主服务器master: ~~~    #vi /etc/my.cnf        [mysqld]        #        log-bin=mysql-bin   #[必须]启用二进制日志        server-id=131      #[必须]服务器唯一ID，默认是1，一般取IP最后一段        #binlog-do-db         =test   #需要备份数据，多个写多行        #binlog-ignore-db    =mysql #不需要备份的数据库，多个写多行 ~~~ 3、修改从服务器slave: ~~~    #vi /etc/my.cnf        [mysqld]        log-bin=mysql-bin   #[不是必须]启用二进制日志        server-id=133      #[必须]服务器唯一ID，默认是1，一般取IP最后一段        #master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)        #replicate-do-db =test #只复制某个库        #replicate-ignore-db=mysql #不复制某个库 ~~~ 4、重启两台服务器的mysql    service mysqld restart 5、在主服务器上建立帐户并授权slave: ~~~    # mysql -uroot -p123456       mysql>GRANT REPLICATION SLAVE ON *.* to 'clevercode'@'%' identified by 'q123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。 ~~~ 6、登录主服务器的mysql，查询master的状态     mysql> show master status;     +------------------+----------+--------------+------------------+     | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |     +------------------+----------+--------------+------------------+     | mysql-bin.000011 |      248 |              |                  |     +------------------+----------+--------------+------------------+     1 row in set (0.00 sec)    注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化 7、配置从服务器Slave： ~~~    mysql>stop slave;    mysql>change master to master_host='192.168.142.131',master_user='clevercode',master_password='q123456',          master_log_file='mysql-bin.000011',master_log_pos=248;   //注意不要断开，248数字前后无单引号。    Mysql>start slave;    //启动从服务器复制功能，如果slave是启动状态，需要先stop slave； ~~~ 8、检查从服务器复制功能状态： ~~~ mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.142.131 //主服务器地址                   Master_User: clevercode //授权帐户名，尽量避免使用root                   Master_Port: 3306 //数据库端口，部分版本没有此行                 Connect_Retry: 60               Master_Log_File: mysql-bin.000012           Read_Master_Log_Pos: 107 //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos                Relay_Log_File: centos64idx4-relay-bin.000002                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000012              Slave_IO_Running: Yes  //此状态必须YES             Slave_SQL_Running: Yes //此状态必须YES               Replicate_Do_DB:            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 107               Relay_Log_Space: 416               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0 //主从延时。 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 131 1 row in set (0.00 sec) ~~~ 注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。 9、主从服务器测试： 1、主服务器Mysql，建立数据库，并在这个库中建表插入一条数据： ~~~ mysql> create database db_clevercode; Query OK, 1 row affected (1.77 sec) mysql> use db_clevercode; Database changed mysql> create table tb_test(id int(3),name char(10));      Query OK, 0 rows affected (14.17 sec) mysql> insert into tb_test values(1,'clevercode'); Query OK, 1 row affected (1.60 sec) ~~~ 2、从服务器Mysql查询： mysql> show databases;      +--------------------+ | Database           | +--------------------+ | information_schema | | db_clevercode      | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec) mysql> use db_clevercode; Database changed mysql> select * from tb_test; +------+------------+ | id   | name       | +------+------------+ |    1 | clevercode | +------+------------+ 1 row in set (0.01 sec) 10、主从同步延时：      通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。其值有这么几种： NULL — 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。 0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。 正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。

CleverCode最近在导出报表的时候，在整数做减法的时候，发现整数减去null得到是null。这是一个细节问题，希望大家以后注意。 ### 一，表中的数据 total，used都是整形，允许为空。  ### 二，有问题的运算语句 减法问题 ~~~ select id, total, used, (total - used) as have from test_table ~~~  ### 三，正确的写法 使用ifnull，来处理null。 ~~~ select id, ifnull(total,0) as total, ifnull(used,0) as used, (ifnull(total,0) - ifnull(used,0)) as have from test_table ~~~  ### 四，建议 1） 在设计表的时候，如果发现这列是需要运算的列，建议设置默认值，比如0； 2） 在整形列的运算的时候，不管是加，减，乘，除等。都需要使用ifnull，对列进行处理。否则报表数据就会错。 这些都是细节小问题，大家一看就能明白，希望大家在细节上注意，否则一份财务表报计算出错了，那后果是很严重的！ ### 版权声明： 1）原创作品，出自"CleverCode的博客"，请勿转载，否则追究版权法律责任。 2）原创地址：[http://blog.csdn.net/clevercode/article/details/47035975](http://blog.csdn.net/clevercode/article/details/47035975)。 3）分类地址（Mysql数据库总结）：[http://blog.csdn.net/clevercode/article/category/3262205](http://blog.csdn.net/clevercode/article/category/3262205)（博客持续增加，关注请收藏） 4）欢迎大家关注我博客更多的精彩内容：[http://blog.csdn.net/CleverCode](http://blog.csdn.net/CleverCode)。

CleverCode在实际的工作中，经常需要将一些报表，或者日志数据等导出来，如果直接做页面，如果次数也不是很多，需求也不同。所以直接导出csv文件，更加直观。 ### 一，导出csv文件 #### 1.1 语句格式 SELECT [列名] FROM table [WHERE 语句] [order by 语句] [limit 语句] INTO OUTFILE '目标文件' [OPTION]; #### 1.2 参数说明 该语句分为两个部分。前半部分是一个普通的SELECT语句，通过这个SELECT语句来查询所需要的数据；后半部分是导出数据的。其中，“目标文件”参数指出将查询的记录导出到哪个文件中；“OPTION”参数为可选参数选项，其可能的取值有： - fields terminated by '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符。默认值是“\t”。 - fields enclosed by '字符'：设置字符来括住字段的值，只能为单个字符。默认情况下不使用任何符号。 - fields optionally enclosed by '字符'：设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。 - fields escaped by '字符'：设置转义字符，只能为单个字符。默认值为“\”。 - lines starting by '字符串'：设置每行数据开头的字符，可以为单个或多个字符。默认情况下不使用任何字符。 - lines terminated by '字符串'：设置每行数据结尾的字符，可以为单个或多个字符。默认值是“\n”。 #### 1.3 重要提示 into outfile ‘目标文件’，目标文件是保存在mysql的服务器端，因为sql语句都是在服务器端执行了。所以执行完后目标文件会保存在mysql服务器端。into outfile '/tmp/test.csv'，一般是mysql服务器是在linux上；into outfile 'E:/test.csv' ，一般是mysql服务器端在windows上。并且目标文件必须有写入权限，而且文件不能存在。 #### 1.4 mysql服务器安装在linux举例 ~~~ select * from proxy_list order by id asc limit 0,2 into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n'; ~~~ #### 1.5 mysql服务器安装在windows举例 ~~~ select uid, nation from system_user order by uid asc limit 0,2 into outfile 'e:/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n'; ~~~ #### 1.6 错误举例 1 无写入权限  2 文件已经存在  ### 版权声明： 1）原创作品，出自"CleverCode的博客"，请勿转载，否则追究版权法律责任。 2）原创地址：[http://blog.csdn.net/clevercode/article/details/46965115](http://blog.csdn.net/clevercode/article/details/46965115)。 3）分类地址（Mysql数据库总结）：[http://blog.csdn.net/clevercode/article/category/3262205](http://blog.csdn.net/clevercode/article/category/3262205)（博客持续增加，关注请收藏） 4）欢迎大家关注我博客更多的精彩内容：[http://blog.csdn.net/CleverCode](http://blog.csdn.net/CleverCode)。

CleverCode的同事最近给我推荐了一个分析mysql中sql语句的工具profiling，发现这个工具非常不错，能够很准确的分析出查询的过程中sql语句具体的时间花在了哪里。CleverCode在这里总结一下，分享给大家。 【 CleverCode在csdn博客中的原创作品，请勿转载，原创地址：[http://blog.csdn.net/clevercode/article/details/46310835](http://blog.csdn.net/clevercode/article/details/46310835)】 ### 一，简介     MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。      MySQL5.0.37版本以上支持PROFILING调试功能，让您可以了解SQL语句消耗资源的详细信息。因为它需要调用系统的getrusage()函数，所以只是在Linux/Unix类平台上才能使用，而不能在Windows平台上使用。而且，PROFILING是针对处理进程(process)而不是线程(thread)的，服务器上的其他应用，可能会影响您的调试结果，因此，这个工具适合开发过程中的调试，如果要在生产环境中调试使用，则要注意它的局限性。 ### 二，2 操作 2.1 查看是否已经启用profile，默认是关闭的。 ~~~ mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) ~~~ 2.2 启用profiling。变量profiling是用户变量，每次都得重新启用。 ~~~ mysql> set profiling = 1;   Query OK, 0 rows affected (0.00 sec) mysql> select @@profiling;   +-------------+ | @@profiling | +-------------+ |           1 |  +-------------+ 1 row in set (0.00 sec) ~~~ 2.3 执行以下语句。为避免之前已经把 SQL 存放在 QCACHE 中， 建议在执行 SQL 时， 强制 SELECT 语句不进行 QCACHE 检测。这样可以提交分析的准确性。 ~~~ mysql> use db_user; mysql> select sql_no_cache count(*) from system_user; mysql> update system_user set username = 'CleverCode' where id = 10; mysql> select sql_no_cache count(*) from system_user where age > 20; ...... ~~~ 2.4 使用show profile查询最近一条语句的执行信息。（分析：select sql_no_cache count(*) from system_user where age > 20） ~~~ mysql> show profile; ~~~  2.5 使用show profiles。查看在服务器上执行语句的列表。(查询id，花费时间，语句) 。 ~~~ mysql> show profiles; ~~~  2.6 使用show profile查询制定ID的执行信息。这里分析ID为6的语句。（分析：select sql_no_cache count(*) from system_user where age > 20）。 ~~~ mysql> show profile for query 6; ~~~  2.7 获取 CPU 和 Block IO 的消耗。 ~~~ mysql> show profile block io,cpu for query 6; ~~~  2.8 获取其他信息。都可以通过执行 “SHOW PROFILE *** FOR QUERY n” 来获取。参考地址：[http://dev.mysql.com/doc/refman/5.6/en/show-profile.html](http://dev.mysql.com/doc/refman/5.6/en/show-profile.html)。 ~~~ mysql> show profile all for query 6; mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6; ~~~ ALL displays all information BLOCK IO displays counts for block input and output operations CONTEXT SWITCHES displays counts for voluntary and involuntary context switches CPU displays user and system CPU usage times IPC displays counts for messages sent and received MEMORY is not currently implemented PAGE FAULTS displays counts for major and minor page faults SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs SWAPS displays swap counts **版权声明：** 1）原创作品，出自"CleverCode的博客"，请勿转载，否则追究版权法律责任。 2）原创地址：[http://blog.csdn.net/clevercode/article/details/46310835](http://blog.csdn.net/clevercode/article/details/46310835)。 3）分类地址（Mysql数据库总结）：[http://blog.csdn.net/clevercode/article/category/3262205](http://blog.csdn.net/clevercode/article/category/3262205)（博客持续增加，关注请收藏） 4）欢迎大家关注我博客更多的精彩内容：[http://blog.csdn.net/CleverCode](http://blog.csdn.net/CleverCode)。

### 一， 攻击分析 CleverCode的运维同事给我说，他在查看mysql的错误日志的时候，发现有大量的下图的错误，所以有人是在正对某个地址进行攻击。大量出现这个错误的原因是mysql在limit不支持负数。通常我们分页的时候，url一般都是写成http://xxx.com?page=1&pageSize=20，即获取第一页数据。每页20行。但是如果传入的是http://xxx.com?page=-1&pageSize=20。就会出现以下错误。  ### 二， 有问题的PHP代码 ~~~ function getUserLoginLog($page,$pageSize){ //参数检查 if(!is_int($page) || !is_int($pageSize)){ return; } $start = ($page - 1) * $pageSize; $sqlStr = "select * from user_login_log order by id desc limit $start,$pageSize"; //执行sql语句 //..... //如果page=-1,pageSize=20,以上语句就会变成 //$sqlStr = "select * from user_login_log order by id desc limit -40,20"; } ~~~ ### 三，防止攻击的PHP代码 只需要判断$page,$pageSize为正整数即可。 ~~~ function getUserLoginLog($page,$pageSize){ //参数检查 if(!is_int($page) || !is_int($pageSize)){ return; } //正整数检查 if($page < 1 || $pageSize < 1){ return; } $start = ($page - 1) * $pageSize; $sqlStr = "select * from user_login_log order by id desc limit $start,$pageSize"; //执行sql语句 //..... } ~~~ **版权声明：** 1）原创作品，出自"CleverCode的博客"，转载时请务必注明以下原创地址，否则追究版权法律责任。 2）原创地址：[http://blog.csdn.net/clevercode/article/details/45935593](http://blog.csdn.net/clevercode/article/details/45935593)（转载务必注明该地址）。 3）分类地址：[http://blog.csdn.net/clevercode/article/category/3262205](http://blog.csdn.net/clevercode/article/category/3262205)（博客持续增加，关注请收藏） 4）欢迎大家关注我博客更多的精彩内容：[http://blog.csdn.net/CleverCode](http://blog.csdn.net/CleverCode)。

### 一， Mysql简介 MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策（本词条“授权政策”），它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配PHP和Apache可组成良好的开发环境。 ### 二， Mysql下载     1）下载cmake（MySQL编译工具）     wget http://www.cmake.org/files/v2.8/cmake-2.8.8.tar.gz     2）下载Mysql。由于版本更新可能以前版本已不存在。进入mysql查找即可。    http://dev.mysql.com/Downloads/MySQL-5.5/mysql-5.5.27.tar.gz     3）本次安装所有的软件资源包下载地址     [http://download.csdn.net/detail/clevercode/8662323](http://download.csdn.net/detail/clevercode/8662323) ### 三， Mysql安装 #### 3.1 配置防火墙     1) 在防火墙配置文件中添加一行3306端口 ~~~ # vi /etc/sysconfig/iptables   #编辑防火墙配置文件       -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT（允许3306端口通过防火墙） ~~~     2）重启防火墙 ~~~     # /etc/init.d/iptables restart  #最后重启防火墙使配置生效     ~~~ #### 3.2 安装cmake     1) 解压。安装前确保进行安装Linux常备支持库,Linux中必备常用支持库的安装：[http://blog.csdn.net/clevercode/article/details/45438401](http://blog.csdn.net/clevercode/article/details/45438401)。 ~~~     # cd /usr/local/src/mysql     # tar zxvf cmake-2.8.8.tar.gz     # cd cmake-2.8.8 ~~~          2) 配置 ~~~     # ./configure ~~~          3) 编译 ~~~     # make ~~~     4) 安装 ~~~     # make install     ~~~          5 )查看版本 ~~~     # cmake -version ~~~ #### 3.3 安装Mysql 1) 创建用户 ~~~  # groupadd mysql  #添加mysql组      # useradd -g mysql mysql -s /bin/false  #创建用户mysql并加入到mysql组，不允许mysql用户直接登录系统 ~~~ 2) 配置MySQL数据库存放目录与安装目录 ~~~  # mkdir -p /data0/mysql  #创建MySQL数据库存放目录      # chown -R mysql:mysql /data0/mysql   #设置MySQL数据库目录权限  # mkdir -p /usr/local/mysql #创建MySQL安装目录 ~~~ 3) 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf mysql-5.5.27.tar.gz  # cd mysql-5.5.27 ~~~ 4) 配置 ~~~  # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DMYSQL_DATADIR=/data0/mysql  -DSYSCONFDIR=/etc ~~~ 5）编译 ~~~  # make ~~~ 6)安装 ` # make install ` 7) 配置my.cnf 方式一：手动修改 ~~~  # cd /usr/local/mysql      # cp ./support-files/my-huge.cnf  /etc/my.cnf    #拷贝配置文件（注意：如果/etc目录下面默认有一个my.cnf，直接覆盖即可）      # vi /etc/my.cnf   #编辑配置文件,在 [mysqld] 部分增加下面一行     datadir = /data0/mysql  #添加MySQL数据库路径     :wq!  #保存退出   ~~~ 方式二：使用已经配置好的文件（配置文件下载地址：  [http://download.csdn.net/detail/clevercode/8662323](http://download.csdn.net/detail/clevercode/8662323)） 备份 ~~~  # cp /etc/my.cnf /etc/my.cnf_bak_20141117  # cd /etc  # rz my.cnf ~~~ 8) 生成mysql系统数据库 ~~~  # cd /usr/local/mysql  # ./scripts/mysql_install_db --user=mysql ~~~ 9) 把Mysql加入系统启动  ~~~  # cp ./support-files/mysql.server  /etc/rc.d/init.d/mysqld  #把Mysql加入系统启动      # chmod 755 /etc/init.d/mysqld   #增加执行权限     /******* chkconfig mysqld on ****/ #设置开机启动 (如果机器重启，最好手动重启服务，因为不知道哪些服务需要先起来，所以一般不加入开机启动，所以注释)   # vi /etc/rc.d/init.d/mysqld  #编辑     basedir=/usr/local/mysql   #MySQL程序安装路径     datadir=/data0/mysql  #MySQl数据库存放目录     service mysqld start  #启动 ~~~ 10) 把mysql服务加入系统环境变量 ~~~  # vi /etc/profile   #把mysql服务加入系统环境变量：在最后添加下面这一行     export PATH=$PATH:/usr/local/mysql/bin     :wq! #保存退出 ~~~ 11) 指定mysql的库文件地址 下面这两行把myslq的库文件链接到系统默认的位置，在编译类似PHP等软件时可以不用指定mysql的库文件地址。     ~~~  # ln -s /usr/local/mysql/lib/mysql /usr/lib/mysql      # ln -s /usr/local/mysql/include/mysql /usr/include/mysql ~~~ 12) 修改Mysql的root密码。安装完mysql后，root的默认密码为空。 方式一： mysql默认root用户没有密码，输入mysql -uroot 进入mysql ~~~  # mysql -uroot mysql>update user set password=PASSWORD(‘123456’) where User='root'; mysql>flush privileges； ~~~ 方式二： 1)如果是刚安装完mysql或者密码为空，是用以下方式修改。 ` # /usr/local/mysql/bin/mysqladmin -u root password "123456" ` 2) 如果不是刚安装完并且密码不为空，使用以下方式。即加入-p,提示需要输入旧密码。 ` # /usr/local/mysql/bin/mysqladmin -u root -p password "123456"` 13) 重启 ~~~  # service mysqld restart ~~~ ### 四， Mysql启动停止重启     1) 状态 `     # service mysqld status `          2) 启动 `     # service mysqld start `          3)停止 `     # service mysqld stop `          4) 重启     `     # service mysqld restart  `          5) 查看3306端口监听状态 `     # netstat -an | grep 3306 ` ### 五， Mysql配置远程登录     1）配置所有IP可以访问 ~~~         #mysql -uroot -p123456         mysql> grant all privileges on  *.* to 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;         mysql> flush privileges; ~~~          2)配置制定IP可以访问         如果你想允许用户jack从ip为10.10.50.127的主机连接到mysql服务器，并使用654321作为密码 ~~~         mysql>GRANT ALL PRIVILEGES ON *.* TO 'jack'@’101.10.50.127’ IDENTIFIED BY '654321' WITH GRANT OPTION;         mysql>FLUSH RIVILEGES; ~~~          3)配置一个类似root用户可以有所有权限的用户 ~~~         mysql> grant all privileges on  *.* to 'clevercode'@'%' IDENTIFIED BY 'clevercode#er239' WITH GRANT OPTION;         mysql> flush privileges;      ~~~ ### 六，6 Mysql查看位数  ~~~     mysql> show variables like '%version_%';     version_compile_machine：i686 为32位，x86_64为64位。 ~~~  ### 七， Mysql状态查看  mysql> status;  ### 八，Linux中连接远程mysql数据库 用root用户，密码为123456，连接IP：192.168.1.100，端口为3306。 ` # mysql -uroot -p123456 -h 192.168.1.100 -P3306` ### 九，Navicat连接远程mysql设置 1) 连接配置。（单击连接）  2）编码配置。（选择连接的页签，单击右键，选择连接属性） 如果在写入的时候是gbk，那么使用navicat读出来的时候也应该使用gbk，不用管数据库是用什么编码存放的。如果写入的时候是utf8,那么客户端读取数据的时候也应该使用utf8.如下图，php写入的时候是gbk，那么navicat读出数据需要用gbk编码。设置为：936 (ANSI/OEM - Simplified Chinese GBK)  **版权声明：** 1）原创作品，出自"CleverCode的博客"，转载时请务必注明以下原创地址，否则追究版权法律责任。 2）原创地址：[http://blog.csdn.net/clevercode/article/details/45499231](http://blog.csdn.net/clevercode/article/details/45499231)（转载务必注明该地址）。 3）博客专栏地址(Linux常用软件安装与配置)：[http://blog.csdn.net/column/details/linuxsoftwareinstall.html](http://blog.csdn.net/column/details/linuxsoftwareinstall.html)（持续增加，关注请收藏）。 4）欢迎大家关注我博客更多的精彩内容：[http://blog.csdn.net/CleverCode](http://blog.csdn.net/CleverCode)。

> 原文出处：[Mysql数据库总结](http://blog.csdn.net/column/details/mysqlsummary.html) > 作者：[clevercode](http://blog.csdn.net/clevercode) **本系列文章经作者授权在看云整理发布，未经作者允许，请勿转载！** # Mysql数据库总结 > Mysql数据库的安装与配置，常见的一些优化技巧，mysql攻击与防御分析，以及平时写sql语句时候的一些常见错误分析。