搜索词>>mysql5.6 耗时0.0050
  • centos6 安装mysql 5.6 数据库及配置

    centos6.5安装MySQL 5.6版本,并配置数据了链接数量数据库默认字符集utf8mb4支持Emoji表情4字节内容<h2><span style="font-family:宋体">一、用户切换</span></h2> <p><span style="font-family:宋体">通过远程工具链接到</span>centos6.5<span style="font-family:宋体">服务器后</span>,<span style="font-family:宋体">切换至</span>root<span style="font-family:宋体">用户。如果已经是</span>root<span style="font-family:宋体">用户则忽略该步骤。</span></p> <p><span style="font-family:宋体">切换</span>root<span style="font-family:宋体">用户步骤</span>:</p> <p><span style="font-family:宋体">执行命令</span>:</p> <pre> <code class="language-html">su –</code></pre> <p><span style="font-family:宋体">回车</span></p> <p><span style="font-family:宋体">输入</span>root<span style="font-family:宋体">密码</span></p> <p> </p> <p><span style="font-family:宋体">密码输入成功则已经切换至</span>root<span style="font-family:宋体">用户,失败则提示鉴定错误。重新执行上面的命令直到密码正确进入</span>root<span style="font-family:宋体">用户</span></p> <p><span style="font-family:宋体">切换成功后可以见到由</span>$<span style="font-family:宋体">变成了</span>#</p> <h2><span style="font-family:宋体">二、检查系统中是否存在</span>MySQL<span style="font-family:宋体">或者</span>MySQL<span style="font-family:宋体">依赖</span></h2> <p><span style="font-family:宋体">检查当前系统是否已经安装</span>MySQL<span style="font-family:宋体">或者</span>MySQL<span style="font-family:宋体">的相关库</span>,<span style="font-family:宋体">如果已经安装</span>MySQL<span style="font-family:宋体">数据库则询问相关人员是否允许卸载重新安装。如果存在</span>MySQL<span style="font-family:宋体">的依赖库则必须先卸载,否则后面可能会因为当前依赖库的版本与后面安装的版本不一致导致安装冲突,最终导致安装失败。</span></p> <p><span style="font-family:宋体">查看系统是否安装</span>MySQL<span style="font-family:宋体">相关</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@localhost ~]# rpm -qa|grep mysql mysql-libs-5.1.71-1.el6.x86_64 [root@localhost ~]#</code></pre> <p><img alt="删除历史MySQL相关包" class="img-thumbnail" src="/assist/images/blog/331bd33c6c5e4186ae0a1e339f1d9985.png" /></p> <p><span style="font-family:宋体">当前系统没有安装</span>MySQL<span style="font-family:宋体">但是有一个依赖包</span>,<span style="font-family:宋体">将其先卸载</span></p> <p><span style="font-family:宋体">卸载命令</span>:</p> <pre> <code class="language-html">[root@localhost ~]# rpm -e --nodeps  mysql-libs-5.1.71-1.el6.x86_64</code></pre> <p><span style="font-family:宋体">查看是否已经卸载</span>,<span style="font-family:宋体">可执行上面的查看命令</span></p> <h2><span style="font-family:宋体">三、安装</span>MySQL<span style="font-family:宋体">数据库</span></h2> <p><span style="font-family:宋体">通过</span>yum<span style="font-family:宋体">安装</span>MySQL<span style="font-family:宋体">(注意系统必须连接外网)</span></p> <h3>3.1<span style="font-family:宋体">首先安装</span>MySQL<span style="font-family:宋体">的</span>yum<span style="font-family:宋体">源(</span>MySQL yum<span style="font-family:宋体">源官方地址</span>https://dev.mysql.com/downloads/repo/yum/<span style="font-family:宋体">)</span></h3> <p><span style="font-family:宋体">安装命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# rpm -Uvh http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm Retrieving http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm Preparing...                ########################################### [100%]    1:mysql-community-release########################################### [100%] [root@bogon ~]#</code></pre> <p><img alt="安装MySQL yum源" class="img-thumbnail" src="/assist/images/blog/608a9931e82f4cf3982c07e917afc339.png" /></p> <p><span style="font-family:宋体">或者</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">yum install -y http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm</code></pre> <p> </p> <h3>3.2<span style="font-family:宋体">首先使用</span>yum<span style="font-family:宋体">查看可以安装的</span>MySQL<span style="font-family:宋体">版本</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@localhost ~]# yum list|grep mysql</code></pre> <p><img alt="查看可以选择安装的MySQL版本" class="img-thumbnail" src="/assist/images/blog/40a5fdd47d3b4fbe9265bacdda75cb6d.png" /></p> <p> </p> <h3>3.3<span style="font-family:宋体">通过</span>yum<span style="font-family:宋体">安装</span>mysql5.6.37<span style="font-family:宋体">版本</span></h3> <p><span style="font-family:宋体">安装命令:</span></p> <pre> <code class="language-html">[root@localhost ~]# yum install -y mysql-community-server.x86_64</code></pre> <p><img alt="安装MySQL5.6" class="img-thumbnail" src="/assist/images/blog/fc16ad47bb5a47cbba1f9488b989feb3.png" /></p> <h2><span style="font-family:宋体">四、启动</span>MySQL<span style="font-family:宋体">服务</span></h2> <p><span style="font-family:宋体">启动</span>MySQL<span style="font-family:宋体">服务命令</span></p> <pre> <code class="language-html">[root@localhost ~]# service mysqld start</code></pre> <p><span style="font-family:宋体">重点日志</span>:</p> <pre> <code class="language-html">PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:   /usr/bin/mysqladmin -u root password 'new-password'   /usr/bin/mysqladmin -u root -h bogon password 'new-password' Alternatively you can run:   /usr/bin/mysql_secure_installation </code></pre> <p><span style="font-family:宋体">初次启动</span>MySQL<span style="font-family:宋体">服务</span>,<span style="font-family:宋体">会提示执行相关命令设置</span>root<span style="font-family:宋体">用户密码</span></p> <p><img alt="启动MySQL服务" class="img-thumbnail" src="/assist/images/blog/de4ca6d527cf4002b43fa018a9f54dff.png" /></p> <h2><span style="font-family:宋体">五、配置</span>MySQL</h2> <h3>5.1<span style="font-family:宋体">设置</span>MySQL<span style="font-family:宋体">的默认密码</span></h3> <p><span style="font-family:宋体">根据初次的启动提示,进行设置默认密码</span>,<span style="font-family:宋体">当前版本为</span>5.6.37<span style="font-family:宋体">设置默认密码的方式如下</span>:</p> <p><span style="font-family:宋体">将默认密码设置为</span>root</p> <pre> <code class="language-html">[root@localhost ~]# /usr/bin/mysqladmin -u root password root</code></pre> <p> </p> <h3>5.2<span style="font-family:宋体">登录</span>root<span style="font-family:宋体">用户</span></h3> <p><span style="font-family:宋体">登录</span>root<span style="font-family:宋体">命令</span>:</p> <p>[root@localhost ~]# mysql -uroot –proot</p> <p><img alt="root用户登录" class="img-thumbnail" src="/assist/images/blog/81b466cf8bdc476cb066df6109f816d3.png" /></p> <p><span style="font-family:宋体">退出登录命令</span></p> <pre> <code class="language-html">Mysql>exit</code></pre> <h3>5.3<span style="font-family:宋体">配置</span>MySQL<span style="font-family:宋体">的配置文件</span>my.cnf</h3> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.3.1<span style="font-family:宋体">配置数据库字符集</span>utf8mb</h4> <p><span style="font-family:宋体">编辑</span>my.cnf<span style="font-family:宋体">文件</span></p> <pre> <code class="language-html">[root@localhost ~]# vi /etc/my.cnf</code></pre> <p><span style="font-family:宋体">默认内容</span></p> <p><img alt="默认配置文件内容" class="img-thumbnail" src="/assist/images/blog/f4a9a176406b4e129597fcf5fa8c31b1.png" /></p> <p> </p> <p><span style="font-family:宋体">配置字符集</span>utf8mb4</p> <p style="margin-left:18.0pt"><span style="font-family:宋体">配置后内容如下</span>:</p> <pre> <code class="language-html">[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect='SET NAMES utf8mb4'</code></pre> <p><span style="font-family:宋体">修改后内容</span>:</p> <p><img alt="设置字符集" class="img-thumbnail" src="/assist/images/blog/3602876605814779bf0bbf9a20246dec.png" /></p> <p><span style="color:#27ae60"><strong><span style="font-family:宋体">注意</span>:MySQL<span style="font-family:宋体">版本</span>5.5<span style="font-family:宋体">以上才支持</span>utf8mb4</strong></span></p> <p><span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务<br /> 命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# service  mysqld restart Stopping mysqld:                                           [  OK  ] Starting mysqld:                                           [  OK  ] [root@bogon ~]#</code></pre> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.3.2<span style="font-family:宋体">配置</span>MySQL<span style="font-family:宋体">最大连接数</span></h4> <p><span style="font-family:宋体">编辑</span>my.cnf<span style="font-family:宋体">文件,</span></p> <pre> <code class="language-html">[root@localhost ~]# vi /etc/my.cnf</code></pre> <p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点中添加</span></p> <pre> <code class="language-html">max_connections = 1000</code></pre> <p><img alt="设置最大链接数" class="img-thumbnail" src="/assist/images/blog/d9104553fa5d419fbbaf3e7f73490514.png" /></p> <p><span style="font-family:宋体">该配置标识</span>MySQL<span style="font-family:宋体">数据库的最大连接数为</span>1000<span style="font-family:宋体">个<br /> <span style="color:#e74c3c"><strong>注意:配置文件方式修改最大连接数需要重启MySQL服务。某些时候不能重启则可以mysql的root用户登录设置</strong></span></span><br />  </p> <pre> <code class="language-html">mysql> show variables LIKE '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> set global max_connections=500; Query OK, 0 rows affected (0.00 sec) mysql> show variables LIKE '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 500 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> exit </code></pre> <p><span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# service  mysqld restart Stopping mysqld:                                           [  OK  ] Starting mysqld:                                           [  OK  ] [root@bogon ~]# </code></pre> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.3.3<span style="font-family:宋体">配置数据库连接超时时间</span></h4> <p><span style="font-family:宋体">编辑</span>my.cnf<span style="font-family:宋体">文件,</span></p> <pre> <code class="language-html">[root@localhost ~]# vi /etc/my.cnf</code></pre> <p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点中添加</span></p> <pre> <code class="language-html">wait_timeout = 15811200</code></pre> <p><img alt="设置超时时间" class="img-thumbnail" src="/assist/images/blog/da1018e90f42441e91f96364bfe561ce.png" /></p> <p><span style="font-family:宋体">该配置标识数据库连接会话超时时间为半年</span>,<span style="font-family:宋体">防止应用中数据库连接池的闲置连接超时。</span></p> <p> </p> <p><span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# service  mysqld restart Stopping mysqld:                                           [  OK  ] Starting mysqld:                                           [  OK  ] [root@bogon ~]# </code></pre> <h3>5.4<span style="font-family:宋体">创建数据库以及用户</span></h3> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.4.1<span style="font-family:宋体">登录</span>MySQL<span style="font-family:宋体">数据库</span></h4> <p><span style="font-family:宋体">首先通过</span>root<span style="font-family:宋体">用户登录</span>MySQL<span style="font-family:宋体">数据库</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# mysql -uroot –proot</code></pre> <p><span style="font-family:宋体">命令说明</span>:</p> <p>-u <span style="font-family:宋体">后面跟</span>MySQL<span style="font-family:宋体">数据库用户名</span></p> <p>-p <span style="font-family:宋体">后面跟</span>MySQL<span style="font-family:宋体">数据库用户密码</span></p> <p> </p> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.4.2<span style="font-family:宋体">查看</span>MySQL<span style="font-family:宋体">数据库中已有的数据库</span></h4> <p><span style="font-family:宋体">命令</span>:</p> <p>mysql> show databases;</p> <p><img alt="查看默认数据库" class="img-thumbnail" src="/assist/images/blog/86cc4e383e7a4434865d25a1c1436e15.png" /></p> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.4.3<span style="font-family:宋体">创建数据库</span></h4> <p><span style="font-family:宋体">首先通过创建</span>test<span style="font-family:宋体">数据库来熟悉命令</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">create database test default character set utf8mb4 collate utf8mb4_unicode_ci;</code></pre> <p><em><span style="font-family:宋体">注意</span>:Linux</em><em><span style="font-family:宋体">系统中</span>MySQL</em><em><span style="font-family:宋体">区分大小写</span></em></p> <p><img alt="创建数据库" class="img-thumbnail" src="/assist/images/blog/db6cfaa1d6aa4b8c8bf17f668745a3d6.png" /></p> <p><span style="font-family:宋体">创建成功后可查看</span></p> <p><img alt="查看数据库" class="img-thumbnail" src="/assist/images/blog/0dc57780514642f781955816419eced8.png" /></p> <p><span style="font-family:宋体">数据库已经成功创建</span></p> <p> </p> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.4.4<span style="font-family:宋体">创建数据库用户</span></h4> <p><span style="font-family:宋体">为上面创建的</span>test<span style="font-family:宋体">库添加一个数据库用户</span>,<span style="font-family:宋体">并赋予这个用户拥有</span>test<span style="font-family:宋体">库的</span>DBA<span style="font-family:宋体">权限</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">mysql> grant all privileges on test.*  to testuser@'%' identified by '123456'; mysql> grant all privileges on test.*  to testuser@'localhost' identified by '123456';</code></pre> <pre> <code>grant all privileges on test.* to test@'%' identified by '123456' WITH GRANT OPTION;</code></pre> <p><span style="color:#ff0000"><span style="font-family:宋体">添加 WITH GRANT OPTION;表示该用户可以给其他用户进行grant授权。</span></span><br /> <strong><span style="font-family:宋体">注意</span>:<em><span style="font-family:宋体">某些版本例如</span>5.6.37</em></strong><strong><em><span style="font-family:宋体">中</span>localhost</em></strong><strong><em><span style="font-family:宋体">与</span>%</em></strong><strong><em><span style="font-family:宋体">没有交集需要单独配</span>localhost</em></strong><strong><em><span style="font-family:宋体">否则本地无法登陆</span></em></strong></p> <p><img alt="创建MySQL用户" class="img-thumbnail" src="/assist/images/blog/b86c3e1cdd864361927394f7e87c8b58.png" /></p> <p><span style="font-family:宋体">刷新权限</span></p> <pre> <code class="language-html">mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)</code></pre> <p> </p> <p><span style="font-family:宋体">退出当前</span>root<span style="font-family:宋体">用户,使用新创建的</span>testuser<span style="font-family:宋体">用户登录数据库</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# mysql -p192.168.8.167 -utestuser -p123456 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.6.37 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | test               | +--------------------+ 2 rows in set (0.00 sec) mysql></code></pre> <p><img alt="测试用户登录" class="img-thumbnail" src="/assist/images/blog/7462ff3d9482441b84103de62baa2fbe.png" /></p> <p> </p> <h4 style="margin-left:10.5pt; margin-right:10.5pt">5.4.5<span style="font-family:宋体">删除数据库用户</span></h4> <p><span style="font-family:宋体">首先</span>root<span style="font-family:宋体">用户登录数据库</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">[root@bogon ~]# mysql -uroot –proot</code></pre> <p><span style="font-family:宋体">切换至</span>mysql<span style="font-family:宋体">数据库</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql></code></pre> <p><span style="font-family:宋体">删除用户</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">mysql> delete from user where User='testuser' and Host='localhost'; Query OK, 1 row affected (0.00 sec)</code></pre> <p><span style="font-family:宋体">刷新权限</span>:</p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code class="language-html">mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)</code></pre> <p><span style="font-family:宋体">退出后再次使用</span>testuser<span style="font-family:宋体">登录将会失败</span>:</p> <pre> <code class="language-html">[root@bogon ~]# mysql -utestuser -p123456 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES) [root@bogon ~]#</code></pre> <p><br /> <br /> <br /> <strong>注意:</strong><br /> 1.5.6默认关闭了通过ln链接方式的存储路径修改。在/etc/my.cnf文件中可以查看到以下配置和说明</p> <pre> <code class="language-html"># Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 </code></pre> <p>如果要使用ln链接方式修改存放路径注意这里配置<strong><span style="color:#ff0000">值为1,并且关闭selinux</span></strong><br /> <br /> 2.<span style="color:#ff0000"><strong>新装</strong></span>的mysql 5.6(yum方式) 修改数据存放路径</p> <ol> <li>需要先启动mysql服务。生成的默认的/var/lib/mysql文件夹</li> <li>停止mysql服务</li> <li>将生成的mysql文件夹移动到新的你需要移动的地方,并配置/etc/my.cnf文件。将路径指定到新的地方</li> <li>启动mysql服务</li> </ol> <p>直接配置/etc/my.cnf文件有较大几率失败<br />  </p>
  • MySQL5.6配置双机互为主备

    MySQL5.6配置双机互为主备<h2><span style="font-family:宋体">一准备</span></h2> <p><span style="font-family:宋体">首先需要两个</span>MySQL<span style="font-family:宋体">数据库服务器</span></p> <p><span style="font-family:宋体">假如</span>:</p> <p><span style="font-family:宋体">数据库服务器</span>1<span style="font-family:宋体">信息</span></p> <p>192.168.8.202 3306 root root</p> <p><span style="font-family:宋体">数据库服务器</span>2<span style="font-family:宋体">信息</span></p> <p>192.168.8.203 3360 root root</p> <p> </p> <h2><span style="font-family:宋体">二、数据库</span>1<span style="font-family:宋体">配置</span></h2> <h3>2.1<span style="font-family:宋体">修改数据库</span>1<span style="font-family:宋体">配置</span></h3> <p><span style="font-family:宋体">编辑数据库</span>1<span style="font-family:宋体">的配置文件</span>/etc/my.cnf</p> <p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点下添加</span>:</p> <pre> <code>#######主从配置master信息####### #[必须]服务器唯一ID,默认是1,一般取IP最后一段 server_id=202 #[必须]启用二进制日志 log_bin=mysql-bin #需要备份的数据库名  多个库写多行 binlog-do-db=test #忽略备份数据库名 多个库写多行 #binlog-ignore-db=mysql #若涉及及同步函数或者存储过程需要配置,否则主备会产生异常不能同步 log_bin_trust_function_creators=TRUE #######主从配置master信息############ </code></pre> <p> </p> <p><img alt="1" class="img-thumbnail" src="/assist/images/blog/54fb33900cb949a2aea85ef489156cc6.png" /></p> <p> </p> <p><strong><span style="font-family:宋体"><span style="color:red">配置方式为指定数据库</span></span><span style="color:red">test</span></strong><strong><span style="font-family:宋体"><span style="color:red">同步</span></span></strong></p> <h3>2.2<span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务</span></h3> <p><span style="font-family:宋体">重启数据库服务器</span>1<span style="font-family:宋体">的</span>MySQL<span style="font-family:宋体">服务</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>service mysqld restart</code></pre> <h3>2.3<span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></h3> <p><span style="font-family:宋体">创建一个</span>slave<span style="font-family:宋体">用户</span>,<span style="font-family:宋体">用于数据库服务器</span>2<span style="font-family:宋体">上进行同步</span></p> <p> </p> <p>Root<span style="font-family:宋体">登录</span>MySQL<span style="font-family:宋体">数据库</span></p> <pre> <code>[root@localhost ~]# mysql -uroot -proot</code></pre> <p> </p> <p><span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></p> <pre> <code>mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';</code></pre> <p> </p> <p><span style="font-family:宋体">切换至</span>mysql<span style="font-family:宋体">数据库</span>,<span style="font-family:宋体">查看刚才创建的用户信息</span></p> <pre> <code>mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user where User='slave' \G; *************************** 1. row ***************************                   Host: %                   User: slave               Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: Y       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string:       password_expired: N 1 row in set (0.00 sec) ERROR: No query specified mysql></code></pre> <p><img alt="2" class="img-thumbnail" src="/assist/images/blog/d3716e0e60d7453290b3af342cf6878d.png" /></p> <p><span style="font-family:宋体">注意查看</span>: <strong><span style="color:red">Repl_slave_priv: Y</span></strong></p> <p><span style="font-family:宋体">是</span>Y<span style="font-family:宋体">标识该账户已经有权限操作同步了。</span></p> <h3>2.3<span style="font-family:宋体">备份数据库</span></h3> <p><span style="font-family:宋体">该操作主要是备份<strong>数据库服务器</strong></span><strong>1</strong><span style="font-family:宋体">中需要同步的数据库。(这里的同步数据库为</span>test<span style="font-family:宋体">库)</span></p> <p> </p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">锁表为只读状态</span></li> </ol> <p style="margin-left:18.0pt"><span style="font-family:宋体">锁表命令</span>:<br />  </p> <pre> <code>mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.06 sec) </code></pre> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">备份数据库</span>1<span style="font-family:宋体">数据</span></li> </ol> <p><span style="font-family:宋体">退出数据库或者新打开一个</span>ssh<span style="font-family:宋体">连接</span>,<span style="font-family:宋体">执行以下命令备份</span></p> <pre> <code>[root@localhost ~]# mysqldump -uroot -proot test > /root/test-db.sql</code></pre> <p><img alt="3" class="img-thumbnail" src="/assist/images/blog/22d428f5384146fda237607774502347.png" /></p> <p> </p> <p><span style="font-family:宋体">如图已经将</span>test<span style="font-family:宋体">库备份至</span>/root<span style="font-family:宋体">目录</span>,<span style="font-family:宋体">名称为</span>test-db.sql</p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">查看数据库服务器</span>1<span style="font-family:宋体">的</span>master<span style="font-family:宋体">信息</span></li> </ol> <p style="margin-left:18.0pt"><span style="font-family:宋体">登录数据库执行命令</span>:</p> <pre> <code>mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 |      887 | test         |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql></code></pre> <p><img alt="4" class="img-thumbnail" src="/assist/images/blog/74ce9951b1034a9088bc7daf619e1785.png" /></p> <p><strong><span style="font-family:宋体">记住这里的</span>file</strong><strong><span style="font-family:宋体">名称和</span>Position</strong><strong><span style="font-family:宋体">值</span></strong></p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">解锁表</span></li> </ol> <pre> <code>mysql> unlock tables;</code></pre> <p> </p> <h2><span style="font-family:宋体">三、数据库</span>2<span style="font-family:宋体">配置</span></h2> <h3>3.1<span style="font-family:宋体">修改数据库</span>2<span style="font-family:宋体">配置</span></h3> <p><span style="font-family:宋体">编辑数据库</span>1<span style="font-family:宋体">的配置文件</span>/etc/my.cnf</p> <p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点下添加</span>:</p> <pre> <code>#######主从配置master信息####### #[必须]服务器唯一ID,默认是1,一般取IP最后一段 server_id=203 #[必须]启用二进制日志 log_bin=mysql-bin #需要备份的数据库名  多个库以逗号分隔 binlog-do-db=test #忽略备份数据库名 #binlog-ignore-db=mysql #若涉及及同步函数或者存储过程需要配置,否则主备会产生异常不能同步 log_bin_trust_function_creators=TRUE #######主从配置master信息############</code></pre> <h3>3.2<span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务</span></h3> <p><span style="font-family:宋体">重启数据库服务器</span>2<span style="font-family:宋体">的</span>MySQL<span style="font-family:宋体">服务</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>#service mysqld restart</code></pre> <p> </p> <h3>3.3<span style="font-family:宋体">导入数据</span></h3> <p><span style="font-family:宋体">将数据库服务器</span>1<span style="font-family:宋体">中导出的库导入进来</span></p> <p> </p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">远程复制备份文件</span></li> </ol> <pre> <code>[root@bogon ~]# scp root@192.168.8.202:/root/test-db.sql /root/test-db.sql The authenticity of host '192.168.8.202 (192.168.8.202)' can't be established. RSA key fingerprint is 70:bc:ba:69:58:77:3c:4b:8e:75:75:91:35:fa:40:f6. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.8.202' (RSA) to the list of known hosts. root@192.168.8.202's password: test-db.sql                                                                                                                                                                                                                                 100% 2575     2.5KB/s   00:00    [root@bogon ~]# ll total 42432 -rw-------. 1 root root     3330 Jul 19 03:54 anaconda-ks.cfg -rw-r--r--. 1 root root 41789864 Jul 25 07:10 axshool25.sql drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Desktop drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Documents drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Downloads drwxr-xr-x. 4 root root     4096 Jul 24 06:08 fastDFS -rw-r--r--. 1 root root    41364 Jul 19 03:54 install.log -rw-r--r--. 1 root root     9154 Jul 19 03:52 install.log.syslog drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Music drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Pictures drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Public drwxrwxr-x. 6 root root     4096 May 17 08:39 redis-3.2.9 -rw-r--r--. 1 root root  1547695 May 17 08:40 redis-3.2.9.tar.gz drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Templates -rw-r--r--. 1 root root     2575 Jul 26 03:00 test-db.sql drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Videos [root@bogon ~]# 导入数据 [root@bogon ~]# mysql -uroot -proot -B test </root/test-db.sql</code></pre> <p> </p> <h3>3.4<span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></h3> <p>Root<span style="font-family:宋体">用户登录数据库</span></p> <pre> <code>[root@bogon ~]# mysql -uroot -proot</code></pre> <p> </p> <p><span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></p> <pre> <code>mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';</code></pre> <p> </p> <p><span style="font-family:宋体">切换至</span>mysql<span style="font-family:宋体">数据库</span>,<span style="font-family:宋体">查看刚才创建的用户信息</span></p> <pre> <code>mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user where User='slave' \G; *************************** 1. row ***************************                   Host: %                   User: slave               Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: Y       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string:       password_expired: N 1 row in set (0.00 sec) ERROR: No query specified mysql></code></pre> <p><img alt="5" class="img-thumbnail" src="/assist/images/blog/dc22d4ee5d40435599149de8f38a43cb.png" /></p> <p> </p> <h3>3.5<span style="font-family:宋体">配置</span>master</h3> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">首先停止</span>slave</li> </ol> <pre> <code>mysql>stop slave</code></pre> <p> </p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">配置</span>master<span style="font-family:宋体">指向数据库服务器</span>1:</li> </ol> <pre> <code>mysql> change master to       -> master_host='192.168.8.202',      -> master_user='slave',      -> master_password='123456',      -> master_log_file='mysql-bin.000003',      -> master_log_pos=887; Query OK, 0 rows affected, 2 warnings (0.05 sec) mysql></code></pre> <p> </p> <p><em><span style="font-family:宋体">命令说明</span>:</em></p> <p><em>master_host              :master</em><em><span style="font-family:宋体">的主机地址</span></em></p> <p><em>master_user              :master</em><em><span style="font-family:宋体">的备份用户</span></em></p> <p><em>master_password    :master</em><em><span style="font-family:宋体">备份用户密码</span></em></p> <p><em>master_log_file        :</em><em><span style="font-family:宋体">日志文件名</span>,</em><em><span style="font-family:宋体">在第二步骤的</span>2.3</em><em><span style="font-family:宋体">备份的最后一步查看的信息里面</span></em></p> <p><em>master_log_pos       :</em><em><span style="font-family:宋体">日志位置</span>,</em><em><span style="font-family:宋体">同上也在</span>2.3</em><em><span style="font-family:宋体">步骤中</span></em></p> <p> </p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">启动</span>slave</li> </ol> <pre> <code>mysql> start slave;</code></pre> <p> </p> <h3>3.6<span style="font-family:宋体">查看数据库服务</span>2<span style="font-family:宋体">的</span>master<span style="font-family:宋体">信息</span></h3> <pre> <code>mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      120 | test         |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql></code></pre> <p><img alt="6" class="img-thumbnail" src="/assist/images/blog/8a1bdb08a23d4e6ab2731f9751ba140b.png" /></p> <p> </p> <h2><span style="font-family:宋体">四、配置数据库</span>1master</h2> <ol start="7" style="list-style-type:lower-alpha"> <li value="176">Root<span style="font-family:宋体">用户登录数据库</span>1</li> </ol> <pre> <code>#mysql –uroot –proot</code></pre> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">停止</span>slave</li> </ol> <pre> <code>mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql></code></pre> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">配置</span>master<span style="font-family:宋体">指向数据库服务器</span>2</li> </ol> <pre> <code>mysql> change master to       -> master_host='192.168.8.203',      -> master_user='slave',      -> master_password='123456',      -> master_log_file='mysql-bin.000001',      -> master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.08 sec) 启动slave mysql> start slave; Query OK, 0 rows affected (0.00 sec) </code></pre> <h2><span style="font-family:宋体">五、测试</span></h2> <p> </p> <h3>5.1<span style="font-family:宋体">测试数据库</span>1<span style="font-family:宋体">为</span>master<span style="font-family:宋体">创建表同步至数据库</span>2</h3> <p><span style="font-family:宋体">测试步骤</span>:</p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176">Root<span style="font-family:宋体">数据库用户登录数据库服务器</span>1,<span style="font-family:宋体">切换至</span>test<span style="font-family:宋体">库</span></li> </ol> <p><img alt="7" class="img-thumbnail" src="/assist/images/blog/707f5370f77e4420b0c3cf1ca2279405.png" /></p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">创建一张表</span>,<span style="font-family:宋体">名称为</span>t_data</li> </ol> <p><img alt="8" class="img-thumbnail" src="/assist/images/blog/9782fe887f6b42668c646d0f83c9e140.png" /></p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">登录数据库服务器</span>2<span style="font-family:宋体">,切换至数据库</span>test<span style="font-family:宋体">并查看表请情况</span></li> </ol> <p> </p> <p><span style="font-family:宋体">可以看到数据库</span>2<span style="font-family:宋体">中已经存在</span>t_data<span style="font-family:宋体">表格说明表创建已经同步成功</span></p> <p><img alt="9" class="img-thumbnail" src="/assist/images/blog/ac1062d2c431404c986ccd2f70589649.png" /></p> <h3>5.2<span style="font-family:宋体">测试数据库</span>1<span style="font-family:宋体">为</span>master<span style="font-family:宋体">数据内容同步</span></h3> <p><span style="font-family:宋体">测试步骤</span>:</p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">登录数据库</span>1<span style="font-family:宋体">并切换至</span>test<span style="font-family:宋体">库,新增一条数据到上面创建的</span>t_data<span style="font-family:宋体">表中</span></li> </ol> <p><img alt="10" class="img-thumbnail" src="/assist/images/blog/632dc5d7655d4baf957eb3c6bb49b891.png" /></p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">登录数据库</span>2<span style="font-family:宋体">,切换至</span>test<span style="font-family:宋体">库查看</span>t_data<span style="font-family:宋体">表数据是否同步</span></li> </ol> <p><img alt="11" class="img-thumbnail" src="/assist/images/blog/a2bf6397291f4986b911acb359fcf51e.png" /></p> <p><span style="font-family:宋体">可以看到数据库</span>2<span style="font-family:宋体">中的表</span>t_data<span style="font-family:宋体">数据内容已经同步。测试数据同步成功。</span></p> <p> </p> <h3>5.3<span style="font-family:宋体">测试数据库</span>2<span style="font-family:宋体">为</span>master<span style="font-family:宋体">创建表同步</span></h3> <p><span style="font-family:宋体">测试步骤</span>:</p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176">Root<span style="font-family:宋体">数据库用户登录数据库</span>2<span style="font-family:宋体">,并切换至</span>test<span style="font-family:宋体">库,创建一个</span>t_data2<span style="font-family:宋体">的表格</span></li> </ol> <p><img alt="12" class="img-thumbnail" src="/assist/images/blog/c2e37210f12c4d33940a40a63a59a80f.png" /></p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">登录数据库</span>1<span style="font-family:宋体">切换至</span>test<span style="font-family:宋体">库</span>,<span style="font-family:宋体">查看表</span>t_data2<span style="font-family:宋体">是否创建</span></li> </ol> <p><img alt="13" class="img-thumbnail" src="/assist/images/blog/624225953a5d44fca9ff04d18b5caed0.png" /></p> <p><span style="font-family:宋体">可以看到在数据库</span>1<span style="font-family:宋体">中</span>t_data2<span style="font-family:宋体">表已经同步创建,测试表创建同步成功。</span></p> <p> </p> <h3>5.4<span style="font-family:宋体">测试数据库</span>2<span style="font-family:宋体">为</span>master<span style="font-family:宋体">数据内容的同步</span></h3> <p><span style="font-family:宋体">测试步骤</span>:</p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">登录数据库</span>2<span style="font-family:宋体">,并切换至</span>test<span style="font-family:宋体">库向</span>t_data2<span style="font-family:宋体">表中插入一条数据</span></li> </ol> <p><img alt="14" class="img-thumbnail" src="/assist/images/blog/36ca6b577a124f76a1638df072436855.png" /></p> <ol start="7" style="list-style-type:lower-alpha"> <li value="176"><span style="font-family:宋体">登录数据库</span>1<span style="font-family:宋体">切换至</span>test<span style="font-family:宋体">库查看表</span>t_data2<span style="font-family:宋体">中数据是否同步</span></li> </ol> <p><img alt="15" class="img-thumbnail" src="/assist/images/blog/ba2fb40560134b99b3bd58a8f27ab1ad.png" /></p> <p><span style="font-family:宋体">可以看到数据已经同步至数据库</span>1<span style="font-family:宋体">中的</span>test<span style="font-family:宋体">库的</span>t_data2<span style="font-family:宋体">表。数据同步测试成功。</span></p>
  • centos6源码安装MySQL5.6

    centos6源码安装MySQL5.6<p>1.安装mysql5.6依存包</p> <p>2.下载编译包</p> <pre> <code class="language-html">wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz tar xvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz</code></pre> <p>3.复制到指定目录</p> <pre> <code class="language-html">mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql</code></pre> <p>4.创建mysql用户和组</p> <pre> <code class="language-html">groupadd mysql useradd -r -g mysql mysql -d /usr/local/mysql passwd mysql</code></pre> <p>5.修改目录权限</p> <pre> <code class="language-html">chown -R mysql:mysql /usr/local/mysql</code></pre> <p>6.安装数据库</p> <pre> <code class="language-html">su mysql /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data exit</code></pre> <p>7.复制mysql配置文件</p> <pre> <code class="language-html">cd /usr/local/mysql/support-files cp my-default.cnf /etc/my.cnf</code></pre> <p>8.添加系统服务</p> <pre> <code class="language-html">cp mysql.server /etc/init.d/mysql chkconfig mysql on</code></pre> <p>9.添加环境变量</p> <pre> <code class="language-html">vim /etc/profile</code></pre> <p>在最下面添加</p> <pre> <code class="language-html">export MYSQL_HOME="/usr/local/mysql" export PATH="$PATH:$MYSQL_HOME/bin"</code></pre> <p>保存退出后,执行生效代码</p> <pre> <code class="language-html">. /etc/profile</code></pre> <p>10.启动mysql</p> <pre> <code class="language-html">service mysql start</code></pre> <p>11.设置root密码</p> <pre> <code class="language-html">mysqladmin -u root password '123456'</code></pre> <p>12.设置默认端口</p> <pre> <code class="language-html">vim /etc/my.cnf</code></pre> <pre> <code class="language-html">[mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data port=3306 server_id=1 socket=/tmp/mysql.sock</code></pre> <p>13.允许所有外部链接访问(可选)</p> <pre> <code class="language-html">mysql -u root -p</code></pre> <p>mysql命令行输入</p> <pre> <code class="language-html">GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; FLUSH PRIVILEGES;</code></pre> <br /> <br /> 相关文档:<br /> <a rel="" target="_blank"href="http://www.leftso.com/blog/224.html" rel="" target="_blank">yum安装MySQL数据库</a><br />  
  • MySQL5.6数据库双机主从热备配置

    MySQL5.6数据库双机主从热备配置<h2><span style="font-family:宋体">一、准备</span></h2> <p><span style="font-family:宋体">首先安装两个</span>MySQL<span style="font-family:宋体">数据库</span></p> <p>Ip<span style="font-family:宋体">地址和端口分别是</span>:</p> <p>192.168.8.202 3306</p> <p>192.168.8.203 3306</p> <p>Root<span style="font-family:宋体">用户及密码</span></p> <p>root root</p> <p><img alt="逻辑图" class="img-thumbnail" src="/assist/images/blog/87f121d199f44b569be494ca6c9226ca.png" /></p> <p><strong><span style="font-family:宋体"><span style="color:red">保证两个数据库服务器中需要同步的库内容一致</span></span></strong></p> <h2><span style="font-family:宋体">二、配置从数据库</span>Master</h2> <h3>2.1<span style="font-family:宋体">停止</span>master<span style="font-family:宋体">数据库服务</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>#service mysqld stop</code></pre> <p>2.2<span style="font-family:宋体">修改</span>/etc/my.cnf<span style="font-family:宋体">配置文件</span></p> <p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点添加以下内容</span>:</p> <pre> <code>#######主从配置master信息####### #[必须]服务器唯一ID,默认是1,一般取IP最后一段 server_id=202 #[必须]启用二进制日志 log_bin=mysql-bin #需要备份的数据库名  多个库写多行 #binlog-do-db=test #binlog-do-db=test1 #binlog-do-db=test2 #忽略备份数据库名 多个库写多行 binlog-ignore-db=mysql #若涉及及同步函数或者存储过程需要配置,否则主备会产生异常不能同步 log_bin_trust_function_creators=TRUE #######主从配置master信息############</code></pre> <p> </p> <h3>2.3<span style="font-family:宋体">启动</span>MySQL<span style="font-family:宋体">数据库服务</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>#service mysqld start</code></pre> <p>2.4<span style="font-family:宋体">添加从机过来同步数据的用户</span></p> <p><span style="font-family:宋体">首先使用</span>root<span style="font-family:宋体">用户登录</span>master<span style="font-family:宋体">数据库</span>,<span style="font-family:宋体">也就是</span>202</p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>#mysql –uroot –proot</code></pre> <p><span style="font-family:宋体">登录数据库后执行命令</span><br />  </p> <pre> <code>mysql> grant replication slave on *.* to 'slaveuser'@'%' identified by '123456'; Query OK, 0 rows affected (0.06 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.06 sec)</code></pre> <p><strong><span style="font-family:宋体">提示</span></strong><strong>:</strong><em><span style="font-family:宋体">一般不用</span></em><em>root</em><em><span style="font-family:宋体">帐号,“</span></em><em>%</em><em><span style="font-family:宋体">”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端</span></em><em>IP</em><em><span style="font-family:宋体">代替,如</span></em><em>192.168.8.203</em><em><span style="font-family:宋体">,加强安全。</span></em></p> <p> </p> <p><span style="font-family:宋体">查看刚才创建的用户授权结果</span>:</p> <p><span style="font-family:宋体">命令</span>:</p> <p><span style="font-family:宋体">切换至</span>mysql<span style="font-family:宋体">数据库</span></p> <pre> <code>mysql> use mysql mysql>select * from user where host='%' and user='slaveuser' \G;  *************************** 1. row ***************************                   Host: %                   User: slaveuser               Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: Y       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string:       password_expired: N 1 row in set (0.00 sec) ERROR: No query specified mysql></code></pre> <p> </p> <p><img alt="2" class="img-thumbnail" src="/assist/images/blog/42cf2e46dd9449e2a2ef048dd60c3c24.png" /></p> <p> </p> <p><strong>Repl_slave_priv</strong><strong><span style="font-family:宋体">项为</span>Y</strong><strong><span style="font-family:宋体">,表示授权成功</span></strong></p> <p> </p> <h3>2.5<span style="font-family:宋体">查看</span>master<span style="font-family:宋体">数据库状态</span></h3> <p><strong><span style="font-family:宋体">注意</span>:</strong><strong><span style="font-family:宋体">查看之前做好数据库只读操作</span>,</strong><strong><span style="font-family:宋体">防止在配置过程中出现写入数据日志,导致查询的</span>master</strong><strong><span style="font-family:宋体">日志不可控</span></strong></p> <p> </p> <p><span style="font-family:宋体">查看</span>master<span style="font-family:宋体">数据库状态</span></p> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 |     6187 |              | mysql            |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec) mysql></code></pre> <p><img alt="3" class="img-thumbnail" src="/assist/images/blog/487df8f373c743aa85d47462fd11cb76.png" /></p> <p> </p> <p><span style="font-family:宋体">日志文件名</span>:<span style="font-family:宋体">称</span> <strong>mysql-bin.000002</strong></p> <p><span style="font-family:宋体">日志文件位置</span>: 6187</p> <p> </p> <h2><span style="font-family:宋体">三、配置从数据库</span>slave</h2> <p><strong><span style="font-family:宋体"><span style="color:red">如果</span></span><span style="color:red">master</span></strong><strong><span style="font-family:宋体"><span style="color:red">中需要同步的数据库已经存在且有数据</span></span><span style="color:red">,</span></strong><strong><span style="font-family:宋体"><span style="color:red">则需要将</span></span><span style="color:red">master</span></strong><strong><span style="font-family:宋体"><span style="color:red">中的数据库导出到</span></span><span style="color:red">slave</span></strong><strong><span style="font-family:宋体"><span style="color:red">保持</span></span><span style="color:red">master</span></strong><strong><span style="font-family:宋体"><span style="color:red">和</span></span><span style="color:red">slave</span></strong><strong><span style="font-family:宋体"><span style="color:red">需要同步的数据库信息一致。</span></span></strong></p> <h3>3.1<span style="font-family:宋体">停止</span>slave<span style="font-family:宋体">数据库服务</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>#service mysqld stop</code></pre> <p>3.2<span style="font-family:宋体">修改</span>/etc/my.cnf<span style="font-family:宋体">配置文件</span></p> <p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">的节点下添加以下配置</span>:</p> <p><img alt="4" class="img-thumbnail" src="/assist/images/blog/d45976833db9461ca6985770eb53cbc0.png" /></p> <p><strong><span style="font-family:宋体">注意</span>ID</strong><strong><span style="font-family:宋体">不能重复</span></strong></p> <p> </p> <h3>3.3<span style="font-family:宋体">启动</span>slave<span style="font-family:宋体">的</span> MySQL<span style="font-family:宋体">数据库服务</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>#service mysqld start</code></pre> <p>3.4root<span style="font-family:宋体">用户登录从库设置信息</span></p> <p><span style="font-family:宋体">登录数据库</span>:</p> <pre> <code>#mysql –uroot -proot</code></pre> <p><span style="font-family:宋体">停止</span>slave</p> <pre> <code>mysql> stop slave;</code></pre> <p><span style="font-family:宋体">配置</span>master<span style="font-family:宋体">信息</span>:</p> <pre> <code>mysql>change master to   master_host='192.168.8.202',  master_user='slaveuser',  master_password='123456',  master_log_file='mysql-bin.000002',  master_log_pos=6187;</code></pre> <p> </p> <p><em><span style="font-family:宋体">配置说明</span>:</em></p> <p><em>master_host:master</em><em><span style="font-family:宋体">的主机地址</span></em></p> <p><em>master_user:master</em><em><span style="font-family:宋体">上创建的同步数据用户</span>,</em><em><span style="font-family:宋体">之前创建的</span>slaveuser</em></p> <p><em>master_password:master</em><em><span style="font-family:宋体">创建的同步数据用户密码</span></em></p> <p><em>master_log_file:master</em><em><span style="font-family:宋体">最后一步查看的日志文件名</span></em></p> <p><em>master_log_pos:master</em><em><span style="font-family:宋体">最后一步查看的日志当前位置</span>,</em><em><span style="font-family:宋体">同步将从这个点开始</span></em></p> <p> </p> <p><span style="font-family:宋体">启动</span>slave</p> <pre> <code>mysql>start slave</code></pre> <p> </p> <h3>3.5<span style="font-family:宋体">查看从库配置状态</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>Mysql> mysql> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.8.202                   Master_User: slaveuser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002           Read_Master_Log_Pos: 23305                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000002              Slave_IO_Running: Yes             Slave_SQL_Running: 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: 23305               Relay_Log_Space: 457               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: 202                   Master_UUID: 4aed5689-6c69-11e7-9b1f-000c290e4f3d              Master_Info_File: /var/lib/mysql/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0</code></pre> <p>1 row in set (0.00 sec)<img alt="5" class="img-thumbnail" src="/assist/images/blog/9e82bbab3212434b97e01481c2239796.png" /></p> <p><span style="font-family:宋体">注意</span>,<span style="font-family:宋体">上面两个配置</span></p> <p><span style="color:red">Slave_IO_Running: Yes</span></p> <p><span style="color:red">Slave_SQL_Running: Yes</span></p> <p><span style="color:red">Yes</span><span style="font-family:宋体"><span style="color:red">标识已经成功配置</span></span></p> <p> </p> <h2><span style="font-family:宋体">四、测试</span></h2> <h3>4.1<span style="font-family:宋体">表创建同步测试</span></h3> <p><span style="font-family:宋体">测试步骤方法</span>:<span style="font-family:宋体">在</span>master<span style="font-family:宋体">的</span>test<span style="font-family:宋体">数据库中创建一个表</span>,<span style="font-family:宋体">名称为</span>t_book<span style="font-family:宋体">,然后去</span>slave<span style="font-family:宋体">中</span>test<span style="font-family:宋体">数据库查看表是否同步。</span></p> <p> </p> <p><span style="font-family:宋体">首先查看数据库中已经有的表</span>:</p> <p><span style="color:red">Master</span><span style="font-family:宋体"><span style="color:red">中</span></span><span style="color:red">:</span></p> <pre> <code>#mysql –uroot –proot Mysql>use test show tables; +----------------+ | Tables_in_test | +----------------+ | t_user         | +----------------+ 1 row in set (0.00 sec) mysql></code></pre> <p><img alt="6" class="img-thumbnail" src="/assist/images/blog/0bf994d9b7db4ed587e91dc085d0dbe2.png" /></p> <p><span style="font-family:宋体">同样查看</span>slave<span style="font-family:宋体">库中的</span>test<span style="font-family:宋体">库</span></p> <p><img alt="7" class="img-thumbnail" src="/assist/images/blog/034b5e8cf80f47298f9d4801b8cbdeb8.png" /></p> <p> </p> <p><span style="font-family:宋体">在</span>master<span style="font-family:宋体">的</span>test<span style="font-family:宋体">库中创建一个表</span>,<span style="font-family:宋体">名称为</span>t_book</p> <p><span style="font-family:宋体">首先进入</span>MySQL<span style="font-family:宋体">数据库</span></p> <p> </p> <p><span style="font-family:宋体">创建表</span>t_book</p> <pre> <code>mysql> create table t_book(id int,book_name varchar(50)); Query OK, 0 rows affected (0.09 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t_book         | | t_user         | +----------------+ 2 rows in set (0.01 sec) mysql></code></pre> <p> </p> <p><img alt="8" class="img-thumbnail" src="/assist/images/blog/eb32f00cfba042d590533733be10f41f.png" /></p> <p> </p> <p><span style="font-family:宋体">查看</span>slave<span style="font-family:宋体">库中的</span>test</p> <p>Master<span style="font-family:宋体">已经在</span>test<span style="font-family:宋体">库中创建了一个</span>t_book<span style="font-family:宋体">的表格,现在查看</span>slave<span style="font-family:宋体">的</span>test<span style="font-family:宋体">数据库中是否已经同步</span></p> <p> </p> <p><span style="font-family:宋体">登录</span>slave</p> <pre> <code>#root –uroot –proot mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t_book         | | t_user         | +----------------+ 2 rows in set (0.00 sec) mysql></code></pre> <p><img alt="9" class="img-thumbnail" src="/assist/images/blog/4055bd7d4c2c4b87a9db93d542141050.png" /></p> <p><span style="font-family:宋体">可以在</span>slave<span style="font-family:宋体">中看到</span>t_book<span style="font-family:宋体">已经同步过来</span>,</p> <p><img alt="10" class="img-thumbnail" src="/assist/images/blog/98b8f78e613c40f8a12a68007ef00d1e.png" /></p> <p><span style="font-family:宋体">查询数据为空</span></p> <h3>4.2<span style="font-family:宋体">表数据同步测试</span></h3> <p><span style="font-family:宋体">测试方式</span>:</p> <p><span style="font-family:宋体">向</span>Master<span style="font-family:宋体">中的</span>test<span style="font-family:宋体">数据库中的</span>t_book<span style="font-family:宋体">表插入一条测试数据</span>,<span style="font-family:宋体">然后去</span>slave<span style="font-family:宋体">的</span>test<span style="font-family:宋体">数据库查询</span>t_book<span style="font-family:宋体">表中是否存在</span></p> <p> </p> <p>Master<span style="font-family:宋体">操作</span>:</p> <p><img alt="master" class="img-thumbnail" src="/assist/images/blog/2620001766974224ba9cd69408c9cdc6.png" /></p> <p> </p> <p>Slave<span style="font-family:宋体">操作</span>:</p> <p><img alt="slave" class="img-thumbnail" src="/assist/images/blog/b14bda207a9f432aae84d581f7ad8907.png" /></p> <p> </p> <p>Slave<span style="font-family:宋体">中的数据已经同步。</span></p> <p> </p>
  • MySQL 安装 windows 5.6版本配置文件无效解决_MySQL安装后配置my.ini无效解决

    MySQL 安装,MySQL 安装了5.6版本,修改安装目录下的默认配置文件重启后配置无效。MySQL安装 windows 5.6版本配置文件无效解决_MySQL安装后配置my.ini无效解决MySQL安装了5.6版本,修改安装目录下的默认配置文件重启后配置无效。一.情况说明首先MySQL的安装版本是5.6,且是从MySQL官方网站下载的最新的MSI格式window的MySQL安装文件。安装成功后MySQL自动集成到windows系统的服务中名称为MySQL56.咋一看,MySQL安装已经成功。就开始了愉快的使用。啥?Java插入字符串到数据库中报错了,居然说不支持这种字符集。第一反应:哦,忘记改配置了得嘛,搜搜搜打开MySQL的安装目录。发现一枚my-default.ini配置文件。先不管啦,打开文件开始配置。在以下节点添加了字符集的配置:[client]default-character-set=utf8mb4#其他配置...[mysql]default-character-set=utf8mb4#...其他配置....[mysqld]character-set-server=utf8mb4#...其他配置....好啦,重启服务。打开MySQL工具,查询了下现在的字符集:show variables like '%char%';  无效图1看到上面的图,我就开始郁闷了,配置居然没有生效。于是乎开始百度--------------------------咦,找到一个,好像说的挺有道理的,应该把my-default.ini文件改成之前我们熟知的my.ini。文章说的他改了就成功了,于是乎我立即去复制了一份改成my.ini.重启MySQL服务.............重启完成这次应该对了吧,让我再用MySQL命令大法来看看字符集呢:show variables like '%char%';  无效图2持续懵逼中。。。看样子国内的消息不灵通啊,但是又请不动谷歌大神。好啦,只好这样啦。微软的小弟来,帮我一把吧,打开bing搜索引擎,选择国际版。搜索了一堆翻译的问题内容......咦,找到一篇英语的。这个说的好像有道理的样子。打开window的服务,找到MySQL56鼠标右键点击属性查看可执行文件路径图1图2​​​​​​​我勒个去,MySQL5.6安装版本居然默认服务指定的配置文件路径是...."C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56突然心中有千万头羊驼在奔跑......二.解决MySQL5.6安装版配置文件设置无效问题通过上面的配置,我们不难发现,MySQL5.6新版本的配置文件默认位置居然是C:\ProgramData\MySQL\MySQL Server 5.6\my.ini已然不是指定的安装目录C:\Program Files\MySQL\MySQL Server 5.6\两个解决办法:2.1直接去修改C:\ProgramData\MySQL\MySQL Server 5.6\my.ini文件2.2修改MySQL服务指定配置文件到自己的配置文件路径如指定到安装目录"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.6\my.ini" MYSQL56
  • centos7中mysql配置my.cnf字符集utf-8,mysql5.6中文乱码

    centos7中mysql配置my.cnf字符集utf-8,mysql5.6中文乱码查看字符集: <pre> <code>mysql> show variables like '%char%'; </code></pre> <br /> mysql通过配置my.cnf(winidow系统为my.ini配置文件),实现服务字符集utf-8<br /> <br /> 首先找到[client]节点(没有就添加节点) <pre> <code>[client] default-character-set=utf8</code></pre> <br /> 找到[mysqld]节点 <pre> <code>character-set-server=utf8</code></pre> <img alt="配置文件" class="img-thumbnail" src="/assist/images/blog/09c02b28dddf46708c2f4fb53072dd3c.png" /><br /> <br /> 最后重启mysql数据库服务<br />   <pre> <code> systemctl restart mysqld.service</code></pre> <br />
  • MySQL索引优化_MySQL索引类型_MySQL索引怎么用怎么创建

    MySQL索引优化,MySQL索引类型,MySQL索引怎么用MySQL索引怎么创建这里将会通过一些简单得sql进行讲解<h2>引言</h2>     本文用于MySQL数据库通过创建索引来调优的讲解。主要讲解的内容有:MySQL索引优化查询/MySQL索引类型/怎么创建MySQL的索引。<br />     通常大型网站单日就可能会产生几十万甚至几百万的数据,对于没有索引的表,单表查询可能几十万数据就是瓶颈。 <h2>一.简单的对比测试</h2>  测试表结构: <pre> <code class="language-sql">--创建一个测试用article表 CREATE TABLE article ( id bigint(20) NOT NULL AUTO_INCREMENT, title CHAR(255) NOT NULL, content LONGTEXT, TIME INT(10), PRIMARY KEY(id) )</code></pre> <p>以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条数据,平均每条数据源都重复大概10万次,表结构比较简单,仅包含一个自增ID,一个char类型,一个text类型和一个int类型,单表2G大小,使用MyIASM引擎。开始测试未添加任何索引。</p> <p>执行下面的SQL语句:</p> <pre> <code class="language-sql">mysql>SELECT id,FROM_UNIXTIME(time) FROM article a WHERE a.title='测试标题';</code></pre> 查询需要的时间非常恐怖的,如果加上联合查询和其他一些约束条件,数据库会疯狂的消耗内存,并且会影响前端程序的执行。这时给title字段添加一个BTREE索引: <pre> <code class="language-sql">mysql> ALTER TABLE article ADD INDEX index_article_tite (title);</code></pre> <h5>MySQL索引的概念</h5> <p>  索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。上述SQL语句,在没有索引的情况下,数据库会遍历全部200条数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”,那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢?(注:一般数据库默认都会为主键生成索引)。</p> <p>索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。</p> <h5>MySQL索引的类型</h5> <h3>1.普通索引</h3> <p>这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。</p> <pre> <code class="language-sql">--直接创建索引 CREATE INDEX index_article_title ON article(title(255)); ---修改表结构方式添加索引 ALTER TABLE article ADD INDEX index_article_title (title); --创建表的时候创建索引 CREATE TABLE article ( id bigint(20) NOT NULL AUTO_INCREMENT, title CHAR(255) NOT NULL, content LONGTEXT, TIME INT(10), PRIMARY KEY(id), INDEX index_article_title (title(255)) ); --删除索引 DROP INDEX index_article_title ON article; </code></pre> <h3>2.唯一索引</h3> <p>与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。<br />  </p> <pre> <code class="language-sql">--创建唯一索引 CREATE UNIQUE INDEX index_article_title ON article(title(255)); --修改表结构方式创建唯一索引 ALTER TABLE article ADD UNIQUE INDEX index_article_title (title(255)); --创建表的时候创建索引 CREATE TABLE article ( id bigint(20) NOT NULL AUTO_INCREMENT, title CHAR(255) NOT NULL, content LONGTEXT, TIME INT(10), PRIMARY KEY(id), UNIQUE index_article_title (title(255)) ); </code></pre>   <h3>3.全文索引(FULLTEXT)</h3> <p>    MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。<br />  </p> <pre> <code class="language-sql">--创建表的时候创建索引 CREATE TABLE article ( id bigint(20) NOT NULL AUTO_INCREMENT, title CHAR(255) NOT NULL, content LONGTEXT, TIME INT(10), PRIMARY KEY(id), FULLTEXT (content) ); --修改表结构添加全文索引 ALTER TABLE article ADD FULLTEXT index_article_content(content); --直接创建索引 CREATE FULLTEXT INDEX index_article_content ON article(content);</code></pre> <p>4. 单列索引、多列索引</p> <p>多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。</p> <p>5. 组合索引(最左前缀)</p> <p>平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步提高MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:</p> <p>–title,time</p> <p>–title</p> <p>为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:<br />  </p> <pre> <code class="language-sql">--使用到上面的索引 SELECT * FROM article WHERE title='测试' AND time=1234567890; SELECT * FROM article WHERE utitle='测试'; --不使用上面的索引 SELECT * FROM article WHERE time=123456789; </code></pre> <h2>二.MySQL索引优化</h2> <p>上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。</p> <h3>1. 何时使用聚集索引或非聚集索引?</h3> <table align="center" border="1" cellpadding="0" cellspacing="0" class="table table-bordered table-hover"> <tbody> <tr> <td>动作描述</td> <td>使用聚集索引</td> <td>使用非聚集索引</td> </tr> <tr> <td>列经常被分组排序</td> <td>使用</td> <td>使用</td> </tr> <tr> <td>返回某范围内的数据</td> <td>使用</td> <td>不使用</td> </tr> <tr> <td>一个或极少不同值</td> <td>不使用</td> <td>不使用</td> </tr> <tr> <td>小数目的不同值</td> <td>使用</td> <td>不使用</td> </tr> <tr> <td>大数目的不同值</td> <td>不使用</td> <td>使用</td> </tr> <tr> <td>频繁更新的列</td> <td>不使用</td> <td>使用</td> </tr> <tr> <td>外键列</td> <td>使用</td> <td>使用</td> </tr> <tr> <td>主键列</td> <td>使用</td> <td>使用</td> </tr> <tr> <td>频繁修改索引列</td> <td>不使用</td> <td>使用</td> </tr> </tbody> </table> <p>事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。其实这个具体用法我还不是很理解,只能等待后期的项目开发中慢慢学学了。</p> <h3>2. 索引不会包含有NULL值的列</h3> <p>只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。</p> <h3>3. 使用短索引</h3> <p>对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。</p> <h3>4. 索引列排序</h3> <p>MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。</p> <h3>5. like语句操作</h3> <p>一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。</p> <h3>6. 不要在列上进行运算</h3> <p>例如:</p> <pre> <code class="language-sql">select * from users where YEAR(adddate)<2007;</code></pre> <p><br /> 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:</p> <pre> <code class="language-sql">select * from users where adddate<’2007-01-01′</code></pre> <p>关于这一点可以围观:<a href="http://www.zendstudio.net/archives/single-quotes-or-no-single-quotes-in-sql-query" rel="external nofollow" target="_blank">一个单引号引发的MYSQL性能损失。</a></p> <p>最后总结一下,MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的,比如我刚才针对text类型的字段创建索引的时候,系统差点就卡死了。</p>
  • Centos MySql数据库找回root密码

    Centos MySql数据库找回root密码1.停止MySQL服务<br /> 2.编辑MySQL配置文件,在[mysqld]节点中添加以下内容 <pre> <code>skip-grant-tables</code></pre> 3.启动MySQL服务<br /> 4.输mysql回车进入mysql<br /> #mysql<br /> <br /> 5.切换至mysql数据库<br /> >use msyql<br /> 6.执行更新命令: <pre> <code>mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; </code></pre> 7.找回密码后将/etc/my.cnf添加的内容注释掉 <pre> <code>#skip-grant-tables</code></pre> 8.重启MySQL服务<br /> service mysqld restart<br /> <br /> 密码找回完成!
  • yum安装jdk1.8-CentOS6.5

    yum安装jdk1.8-CentOS6.5,centos6,jdk8<h2 style="text-align:left">一、yum<span style="font-family:宋体">安装</span>jdk8</h2> <h3>1.1<span style="font-family:宋体">查看当前可以安装的版本</span></h3> <p><span style="font-family:宋体">命令</span>:</p> <p>[root@localhost ~]# yum list|grep jdk</p> <p>copy-jdk-configs.noarch                      1.3-1.el6                   base  </p> <p>java-1.6.0-openjdk.x86_64                    1:1.6.0.41-1.13.13.1.el6_8  base  </p> <p>java-1.6.0-openjdk-demo.x86_64               1:1.6.0.41-1.13.13.1.el6_8  base  </p> <p>java-1.6.0-openjdk-devel.x86_64              1:1.6.0.41-1.13.13.1.el6_8  base  </p> <p>java-1.6.0-openjdk-javadoc.x86_64            1:1.6.0.41-1.13.13.1.el6_8  base  </p> <p>java-1.6.0-openjdk-src.x86_64                1:1.6.0.41-1.13.13.1.el6_8  base  </p> <p>java-1.7.0-openjdk.x86_64                    1:1.7.0.141-2.6.10.1.el6_9  updates</p> <p>java-1.7.0-openjdk-demo.x86_64               1:1.7.0.141-2.6.10.1.el6_9  updates</p> <p>java-1.7.0-openjdk-devel.x86_64              1:1.7.0.141-2.6.10.1.el6_9  updates</p> <p>java-1.7.0-openjdk-javadoc.noarch            1:1.7.0.141-2.6.10.1.el6_9  updates</p> <p>java-1.7.0-openjdk-src.x86_64                1:1.7.0.141-2.6.10.1.el6_9  updates</p> <p>java-1.8.0-openjdk.x86_64                    1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-debug.x86_64              1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-demo.x86_64               1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-demo-debug.x86_64         1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-devel.x86_64              1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-devel-debug.x86_64        1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-headless.x86_64           1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-headless-debug.x86_64     1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-javadoc.noarch            1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-javadoc-debug.noarch      1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-src.x86_64                1:1.8.0.131-0.b11.el6_9     updates</p> <p>java-1.8.0-openjdk-src-debug.x86_64          1:1.8.0.131-0.b11.el6_9     updates</p> <p>ldapjdk.x86_64                               4.18-8.el6                  base  </p> <p>ldapjdk-javadoc.x86_64                       4.18-8.el6                  base</p> <p> </p> <p><span style="font-family:宋体">如果没有看到上面的版本,安装</span>/<span style="font-family:宋体">更新</span>centos<span style="font-family:宋体">的扩展源</span></p> <pre> <code>#yum –y  install epel-release</code></pre> <h3>1.2<span style="font-family:宋体">安装</span>jdk1.8</h3> <p><span style="font-family:宋体">命令:</span></p> <pre> <code>[root@localhost ~]# yum install -y java-1.8.0-openjdk</code></pre> <p><img alt="可用jdk版本" class="img-thumbnail" src="/assist/images/blog/12c8939452534207b9857a8d40bb48c9.png" /></p> <h2>二、<span style="font-family:宋体">检查安装</span></h2> <p><span style="font-family:宋体">命令</span>:</p> <pre> <code>[root@localhost ~]# java –version openjdk version "1.8.0_131" OpenJDK Runtime Environment (build 1.8.0_131-b11) OpenJDK 64-Bit Server VM (build 25.131-b11, mixed mode)</code></pre> <p> </p> <p><em><span style="font-family:宋体">如上显示出</span>jdk</em><em><span style="font-family:宋体">版本标示已经成功安装</span></em></p>
  • MySQL忘记root密码找回windows系统

    windows系统MySQL忘记root密码找回,MySQLwindows系统MySQL忘记root密码找回<br /> <strong>1.停止MySQL服务</strong><br /> <img class="img-thumbnail" alt="停止MySQL服务" src="/assist/images/blog/ab73dcb8-58f5-4f13-be46-a9d15ae97849.jpg" style="height:630px; width:402px" /><br /> <img class="img-thumbnail" alt="停止MySQL服务" src="/assist/images/blog/82bfa3aa-2843-4fc5-9275-ac6993dc8bad.jpg" style="height:230px; width:467px" /><br /> <strong>2.打开两个CMD 命令框</strong><br /> ①其中一个输入: <div>mysqld -nt --skip-grant-tables</div> <img class="img-thumbnail" alt="目录切换" src="/assist/images/blog/80695fbf-ae95-44c8-8dbd-4fb400253233.jpg" style="height:442px; width:677px" /><br /> <img class="img-thumbnail" alt="mysqld -nt --skip-grant-tables" src="/assist/images/blog/c609ec90-8e6c-4e75-8948-131eb615b612.jpg" style="height:442px; width:677px" /><br /> ②在另外一个输入 <div>mysql -u root</div> 这时候进入到了mysql安全模式<br /> 修改密码: <div>mysql>update mysql.user set password=PASSWORD('new_password') where User='root';</div> 刷新权限<br />   <div>mysql>flush privileges;</div> <img class="img-thumbnail" alt="mysql -u root" src="/assist/images/blog/e2b47636-2432-4e86-af62-4962e353a3c5.jpg" style="height:603px; width:669px" /><br /> <strong>3.启动任务管理器,结束mysqld进程</strong><br /> <img class="img-thumbnail" alt="结束mysqld.exe进程" src="/assist/images/blog/f27f36f5-6810-4d8c-9f18-d207851bb8f9.jpg" style="height:447px; width:412px" /><br /> <strong>4.启动mysql服务</strong><br /> <img class="img-thumbnail" alt="启动MySQL服务" src="/assist/images/blog/7ed31dac-bb7a-4f91-b31d-4298e5994f5f.jpg" style="height:301px; width:605px" /><br /> <strong>5.用新的root密码登陆</strong><br /> <img class="img-thumbnail" alt="新密码登陆" src="/assist/images/blog/614633e9-19e9-4faf-a905-08e0dc53230a.jpg" style="height:442px; width:677px" /><br /> 恭喜成功找回root密码<br /> <strong><em>【注意:】在密码找回的整个过程中,MySQL服务是关闭的,成功找回以后再次启动MySQL服务</em></strong><br />