搜索词>>mysql5.5 耗时0.0080
  • MySQL 5.5 docker 部署手册

    运行篇命令:# docker run -d -v /jenkins/mysql55:/var/lib/mysql -p 3306:3306 --name mysql55 -e MYSQL_ROOT_PASSWORD=root mysql:运行篇命令:# docker run -d -v /jenkins/mysql55:/var/lib/mysql -p 3306:3306 --name mysql55 -e MYSQL_ROOT_PASSWORD=root mysql:5.5.62 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci注意:映射目录设置777权限防止权限问题登录篇docker exec -it mysql55 /bin/sh 说明:mysql55 是运行时候指定的容器名称通过上面命令可以直接登录容器里面,然后就像普通MySQL一样操作登录数据库mysql -uroot -proot创建数据库mysql>create database leftso;创建用户grant all privileges on leftso.* to leftso@'%' identified by 'leftso123456' WITH GRANT OPTION;删除数据库drop database leftso;备份命令备份所有库docker exec mysql55 sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /jenkins/mysql55/all-databases.sql ​备份指定库docker exec mysql55 sh -c 'exec mysqldump -d mysql -uroot -p"root"' > /jenkins/mysql55/all-databases.sql说明:-d指定备份库为mysql-p 后面根用户的密码恢复命令①复制数据文件到容器中docker cp /root/mysql-leftso-20201114.sql mysql55:/②执行恢复命令docker exec mysql55 sh -c 'exec mysql -uroot -proot leftso < /mysql-leftso-20201114.sql '③删除容器中的备份文件docker exec mysql55 sh -c 'exec rm -rf /mysql-leftso-20201114.sql'
  • 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>
  • 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>
  • 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>
  • 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 />
  • Spring MVC 5.0

    Spring框架5.0,spring mvc 5.0入门教程。DispatcherServlet的详细讲解配置以及spring mvc5.0的helloword程序<h2>引言</h2>   在本教程中,我们将学习Spring DispatcherServlet类,其职责以及如何使用示例进行配置。spring mvc 5.0基于Java配置 <h2>一.DispatcherServlet是什么</h2> <br />     <code>DispatcherServlet</code> 充当基于Spring的Web应用程序的前端控制器。 它提供了一个请求处理机制,其中实际工作由可配置的委托组件执行。 它从javax.servlet.http.HttpServlet继承,通常在web.xml文件中配置。<br /> <br />     Web应用程序可以定义任意数量的<code>DispatcherServlet</code> 实例。 每个servlet将在其自己的名称空间中运行,使用映射,处理程序等加载它自己的应用程序上下文。只有由ContextLoaderListener加载的根应用程序上下文(如果有)将被共享。 在大多数情况下,应用程序只有一个具有上下文根URL(/)的<code>DispatcherServlet</code> ,也就是说,到达该域的所有请求都将由它处理。<br /> <br />     <code>DispatcherServlet</code> 使用Spring配置类来发现它需要的委托组件,用于请求映射,视图解析,异常处理等。 <h2>二.DispatcherServlet它如何使用WebApplicationContext</h2>     在基于Spring的应用程序中,我们的应用程序对象位于一个对象容器中。 该容器在对象之间创建对象和关联,并管理其完整的生命周期。 这些容器对象被称为Spring管理的bean(或者简单的bean),在Spring世界中容器被称为应用程序上下文(通过类ApplicationContext)。<br /> <br />     WebApplicationContext是一个普通的ApplicationContext的扩展。 它是Web感知的ApplicationContext,即它具有Servlet上下文信息。 当DispatcherServlet被加载时,它查找WebApplicationContext的bean配置文件并初始化它。<br /> <br />     通过访问Servlet上下文,任何实现了ServletConextAware接口的spring bean都可以访问ServletContext实例,并且可以做很多事情。 例如,它可以获取上下文初始化参数,获取上下文根信息并获取Web应用程序文件夹中的资源位置。 <h2>三.基于XML配置的DispatcherServlet(作为参考理解主讲基于Java配置)</h2> 我们来看看典型的DispatcherServlet声明和初始化是怎样的。 <pre> <code class="language-xml"><web-app> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <context-param> <param-name>contextConfigLocation</param-name> <param-value>/WEB-INF/dispatcher-servlet-context.xml</param-value> </context-param> <servlet> <servlet-name>dispatcher-servlet</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value></param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>dispatcher-servlet</servlet-name> <url-pattern>/*</url-pattern> </servlet-mapping> </web-app></code></pre> 在上面的代码中,dispatcher-servlet-context.xml文件将包含所有可用于DispatcherServlet的bean定义和关联。 这些bean定义将覆盖在全局范围内使用相同名称定义的任何bean的定义。 例如: <pre> <code class="language-xml"><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver" > <property name="prefix"> <value>/WEB-INF/views/</value> </property> <property name="suffix"> <value>.jsp</value> </property> </bean> </beans></code></pre> <h2>四.基于JAVA代码配置的DispatcherServlet </h2> 从Servlet 3.0开始,除了web.xml文件中的声明式配置,DispatcherServlet可以通过实现或扩展Spring提供的这三个支持类来编程配置  <ul> <li>WebAppInitializer接口</li> <li>AbstractDispatcherServletInitializer抽象类</li> <li>AbstractAnnotationConfigDispatcherServletInitializer抽象类</li> </ul> 在下面的类中,WebApplicationInitializer确保ApplicationSerializer类被SpringServletContainerInitializer(它本身自动引导)检测到并用来初始化任何Servlet 3容器。<br /> <br /> 引入spring mvc 5.0需要的相关依赖,pom.xml: <pre> <code class="language-xml"><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.leftso.project.demo</groupId> <artifactId>demo-spring5-springmvc5</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <properties> <failOnMissingWebXml>false</failOnMissingWebXml> <spring.version>5.0.1.RELEASE</spring.version> <jstl.version>1.2.1</jstl.version> <tld.version>1.1.2</tld.version> <servlets.version>3.1.0</servlets.version> <jsp.version>2.3.1</jsp.version> </properties> <dependencies> <!-- Spring MVC Dependency --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <!-- JSTL Dependency --> <dependency> <groupId>javax.servlet.jsp.jstl</groupId> <artifactId>javax.servlet.jsp.jstl-api</artifactId> <version>${jstl.version}</version> </dependency> <dependency> <groupId>taglibs</groupId> <artifactId>standard</artifactId> <version>${tld.version}</version> </dependency> <!-- Servlet Dependency --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>${servlets.version}</version> <scope>provided</scope> </dependency> <!-- JSP Dependency --> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>javax.servlet.jsp-api</artifactId> <version>${jsp.version}</version> <scope>provided</scope> </dependency> </dependencies> <build> <sourceDirectory>src/main/java</sourceDirectory> <resources> <resource> <directory>src/main/resources</directory> </resource> </resources> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-war-plugin</artifactId> <version>2.3</version> <configuration> <failOnMissingWebXml>false</failOnMissingWebXml> </configuration> </plugin> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.5.1</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> <plugin> <groupId>org.apache.tomcat.maven</groupId> <artifactId>tomcat7-maven-plugin</artifactId> <version>2.2</version> <configuration> <path>/</path> </configuration> </plugin> </plugins> </build> </project></code></pre> <br /> <br /> 通过Java的方式可以将上面的web.xml写一个配置类: <pre> <code class="language-java">public class ApplicationInitializer implements WebApplicationInitializer { @Override public void onStartup(ServletContext servletContext) throws ServletException { XmlWebApplicationContext appContext = new XmlWebApplicationContext(); appContext.setConfigLocation("/WEB-INF/dispatcher-servlet-context.xml"); ServletRegistration.Dynamic registration = servletContext .addServlet("rootDispatcher", new DispatcherServlet(appContext)); registration.setLoadOnStartup(1); registration.addMapping("/"); } }</code></pre> 基于Java 配置的完整配置<br /> <strong>例子一:</strong> <pre> <code class="language-java">package com.leftso.project.demo.config; import javax.servlet.ServletContext; import javax.servlet.ServletException; import javax.servlet.ServletRegistration; import org.springframework.web.WebApplicationInitializer; import org.springframework.web.context.ContextLoaderListener; import org.springframework.web.context.support.AnnotationConfigWebApplicationContext; import org.springframework.web.servlet.DispatcherServlet; public class ApplicationInitializer implements WebApplicationInitializer { @Override public void onStartup(ServletContext servletContext) throws ServletException { // 创建 Spring ROOT context AnnotationConfigWebApplicationContext rootContext = new AnnotationConfigWebApplicationContext(); rootContext.register(AppConfig.class); // 管理ROOT context生命周期 servletContext.addListener(new ContextLoaderListener(rootContext)); // 创建spring的dispatcher ,servlet's Spring application context AnnotationConfigWebApplicationContext dispatcherContext = new AnnotationConfigWebApplicationContext(); dispatcherContext.register(DispatcherConfig.class); ServletRegistration.Dynamic dispatcher = servletContext.addServlet("dispatcher", new DispatcherServlet(dispatcherContext)); dispatcher.setLoadOnStartup(1); dispatcher.addMapping("/"); } } </code></pre> 在上面的代码中,<code>AppConfig</code> 和<code>DispatcherConfig</code> 类定义了将在Web应用程序上下文中的spring管理的bean。<br /> AppConfig: <pre> <code class="language-java">package com.leftso.project.demo.config; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import com.leftso.project.demo.TestService; @Configuration @ComponentScan(basePackages = { "com.leftso" }) public class AppConfig { // 该类主要是spring 父级容器配置,里面可以定义很多的bean就像在spring-context.xml中一样 // 例如: @Bean public TestService testService() { return new TestService(); } } </code></pre> DispatcherConfig: <pre> <code class="language-java">package com.leftso.project.demo.config; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.servlet.ViewResolver; import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry; import org.springframework.web.servlet.config.annotation.WebMvcConfigurationSupport; import org.springframework.web.servlet.view.InternalResourceViewResolver; import org.springframework.web.servlet.view.JstlView; @Configuration public class DispatcherConfig extends WebMvcConfigurationSupport { // 覆盖父类的配置进行spring mvc的其他详细配置,配置内容可参考xml的spring-mvc /** * * <pre> * [Function]: * JSP 视图解析器 * [Description]: * JSP 视图解析器 * </pre> * * @return JSP 视图解析器 */ @Bean public ViewResolver jspViewResolver() { InternalResourceViewResolver internalResourceViewResolver = new InternalResourceViewResolver(); internalResourceViewResolver.setViewClass(JstlView.class); internalResourceViewResolver.setSuffix(".jsp"); internalResourceViewResolver.setPrefix("/WEB-INF/views/"); internalResourceViewResolver.setOrder(4); return internalResourceViewResolver; } /** * 资源文件处理器 */ @Override public void addResourceHandlers(ResourceHandlerRegistry registry) { registry.addResourceHandler("/resources/**").addResourceLocations("/resources/"); } } </code></pre> <p><strong>例子二:</strong><br /> 基于Java配置的Spring MVC 5.0的配置,spring mvc 5.0为我们提供了一个封装的抽象类,我们实现这个类并指定相关的配置Java就可以了非常方便:</p> <pre> <code class="language-java">package com.leftso.project.demo.config; import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer; /** * Spring MVC 5启动配置 * * @author xqlee * */ public class SpringMVC5AnnotationConfigDispatcherServletInitializer extends AbstractAnnotationConfigDispatcherServletInitializer { @Override protected Class<?>[] getRootConfigClasses() { return new Class<?>[] {}; } @Override protected Class<?>[] getServletConfigClasses() { return new Class<?>[] { WebMvcConfig.class }; } @Override protected String[] getServletMappings() { return new String[] { "/" }; } } </code></pre> <p>WebMvcConfig:<br />  </p> <pre> <code class="language-java">package com.leftso.project.demo.config; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.web.servlet.config.annotation.EnableWebMvc; import org.springframework.web.servlet.config.annotation.WebMvcConfigurer; import org.springframework.web.servlet.view.InternalResourceViewResolver; import org.springframework.web.servlet.view.JstlView; @Configuration @EnableWebMvc @ComponentScan(basePackages = { "com.leftso.project.demo" }) public class WebMvcConfig implements WebMvcConfigurer { @Bean public InternalResourceViewResolver resolver() { InternalResourceViewResolver resolver = new InternalResourceViewResolver(); resolver.setViewClass(JstlView.class); resolver.setPrefix("/WEB-INF/views/"); resolver.setSuffix(".jsp"); return resolver; } } </code></pre> <p>这比起第一种方式少些几句代码。</p> <h2><br /> 五.编写一个测试spring mvc 5.0的controller</h2> <pre> <code class="language-java">package com.leftso.project.demo.controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; @RestController public class TestController { @GetMapping("/sayHello.do") public Object sayHello(String name) { return "Hello," + name; } } </code></pre> 将项目添加到tomcat中,启动tomcat,并在浏览器中访问controller的地址:<br /> <img alt="spring mvc 5.0" class="img-thumbnail" src="/assist/images/blog/e2422615e7924625a55ad38461e09529.png" /><br /> 从上图可以看到我们的<a rel="" target="_blank"href="http://www.leftso.com/blog/305.html" rel="" target="_blank">spring mvc5.0</a>项目已经成功启动.后续将讲解整合其他配置<br />  
  • Spring 5 MVC 整合 Hibernate 5

    在这个Spring5教程中,学习创建Spring 5 MVC Web应用程序,处理表单提交,集成hibernate连接到后端数据库,以及添加用于输入表单字段验证的hibernate验证器。<h2>引言</h2>     在这个Spring5教程中,学习创建Spring 5 MVC Web应用程序,处理表单提交,集成hibernate连接到后端数据库,以及添加用于输入表单字段验证的hibernate验证器。<br /> <br />     我们将创建一个简单的屏幕,我们可以添加用户字段(名称和电子邮件)。 这些细节将首先验证,然后使用休眠存储在HSQL数据库中。 该页面也将列出所有存储的用户。 <h2>一.Spring MVC 5整合hibernate5开发环境准备</h2> <ul> <li>Eclipse Neon.2 +</li> <li>JDK 1.8 +</li> <li>Spring 5.0.0.RELEASE</li> <li>Hibernate 5.2.11.Final</li> <li>Hibernate validator 5.4.1.Final</li> <li>Servlets 3.1.0</li> <li>HSQLDB 1.8.0.10</li> <li>Tomcat 7 maven plugin 2.2</li> </ul> <h2>二.Spring MVC 5整合hibernate5项目结构图</h2> 该项目具有典型的Maven Web应用程序结构。<br /> <img alt="Spring5" class="img-thumbnail" src="/assist/images/blog/2cd6c348063d40caabff4dfdc8e79010.png" /> <h2>三.Spring MVC 5整合hibernate5类图关系</h2> <img alt="" class="img-thumbnail" src="/assist/images/blog/3bb67013c0b84472824f0936c8f82b6e.png" /> <h2>四.Spring MVC 5整合hibernate5 maven依赖</h2> 查找用于在pom.xml文件中运行此示例的项目依赖项。 <pre> <code class="language-xml"><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.howtodoinjava.spring5.demo</groupId> <artifactId>spring5-mvc-hibernate-example</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <properties> <failOnMissingWebXml>false</failOnMissingWebXml> <spring.version>5.0.0.RELEASE</spring.version> <hibernate.version>5.2.11.Final</hibernate.version> <hibernate.validator>5.4.1.Final</hibernate.validator> <c3p0.version>0.9.5.2</c3p0.version> <jstl.version>1.2.1</jstl.version> <tld.version>1.1.2</tld.version> <servlets.version>3.1.0</servlets.version> <jsp.version>2.3.1</jsp.version> <hsqldb.version>1.8.0.10</hsqldb.version> </properties> <dependencies> <!-- Spring MVC Dependency --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <!-- Spring ORM --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring.version}</version> </dependency> <!-- Hibernate Core --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>${hibernate.version}</version> </dependency> <!-- Hibernate-C3P0 Integration --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>${hibernate.version}</version> </dependency> <!-- c3p0 --> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>${c3p0.version}</version> </dependency> <!-- Hibernate Validator --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-validator</artifactId> <version>${hibernate.validator}</version> </dependency> <!-- JSTL Dependency --> <dependency> <groupId>javax.servlet.jsp.jstl</groupId> <artifactId>javax.servlet.jsp.jstl-api</artifactId> <version>${jstl.version}</version> </dependency> <dependency> <groupId>taglibs</groupId> <artifactId>standard</artifactId> <version>${tld.version}</version> </dependency> <!-- Servlet Dependency --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>${servlets.version}</version> <scope>provided</scope> </dependency> <!-- JSP Dependency --> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>javax.servlet.jsp-api</artifactId> <version>${jsp.version}</version> <scope>provided</scope> </dependency> <!-- HSQL Dependency --> <dependency> <groupId>hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>${hsqldb.version}</version> </dependency> </dependencies> <build> <sourceDirectory>src/main/java</sourceDirectory> <resources> <resource> <directory>src/main/resources</directory> </resource> </resources> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.5.1</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> <plugin> <groupId>org.apache.tomcat.maven</groupId> <artifactId>tomcat7-maven-plugin</artifactId> <version>2.2</version> <configuration> <path>/</path> </configuration> </plugin> </plugins> </build> </project></code></pre> <h2>五.配置Spring MVC 5 servlet分发器DispatcherServlet</h2>     随着Servlet 3.0规范的发布,可以用几乎没有xml来配置你的Servlet容器。 为此,Servlet规范中有ServletContainerInitializer。 在这个类中,你可以注册过滤器,监听器,servlet等,就像你在web.xml中一样。<br /> <br />     Spring提供了知道如何处理WebApplicationInitializer类的SpringServletContainerInitializer。 AbstractAnnotationConfigDispatcherServletInitializer类实现了内部实现WebApplicationInitializer的WebMvcConfigurer。 它注册一个ContextLoaderlistener(可选)和DispatcherServlet,并允许您轻松添加配置类来加载这两个类,并将过滤器应用于DispatcherServlet并提供servlet映射。<br />   <pre> <code class="language-java">public class AppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer { @Override protected Class<?>[] getRootConfigClasses() { return new Class[] { HibernateConfig.class }; } @Override protected Class<?>[] getServletConfigClasses() { return new Class[] { WebMvcConfig.class }; } @Override protected String[] getServletMappings() { return new String[] { "/" }; } }</code></pre> <h2>六.Spring Web MVC 5 配置</h2> Spring Web MVC配置如下。 <pre> <code class="language-java">import org.springframework.context.MessageSource; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.context.support.ResourceBundleMessageSource; import org.springframework.validation.Validator; import org.springframework.validation.beanvalidation.LocalValidatorFactoryBean; import org.springframework.web.servlet.config.annotation.EnableWebMvc; import org.springframework.web.servlet.config.annotation.WebMvcConfigurer; import org.springframework.web.servlet.view.InternalResourceViewResolver; import org.springframework.web.servlet.view.JstlView; @Configuration @EnableWebMvc @ComponentScan(basePackages = { "com.howtodoinjava.demo.spring"}) public class WebMvcConfig implements WebMvcConfigurer { @Bean public InternalResourceViewResolver resolver() { InternalResourceViewResolver resolver = new InternalResourceViewResolver(); resolver.setViewClass(JstlView.class); resolver.setPrefix("/WEB-INF/views/"); resolver.setSuffix(".jsp"); return resolver; } @Bean public MessageSource messageSource() { ResourceBundleMessageSource source = new ResourceBundleMessageSource(); source.setBasename("messages"); return source; } @Override public Validator getValidator() { LocalValidatorFactoryBean validator = new LocalValidatorFactoryBean(); validator.setValidationMessageSource(messageSource()); return validator; } }</code></pre> <ol> <li><code>WebMvcConfigurer</code> 定义了通过使用@EnableWebMvc自定义或添加到默认的<code>@EnableWebMvc</code>配置的选项。</li> <li><code>@EnableWebMvc</code> 启用默认的Spring MVC配置,并注册DispatcherServlet所期望的Spring MVC基础架构组件。</li> <li><code>@Configuration</code> 指示一个类声明了一个或多个@Bean方法,并且可以被Spring容器处理,以在运行时为这些bean生成bean定义和服务请求。</li> <li><code>@ComponentScan</code> 注释用于指定要扫描的基本包。任何用@Component和@Configuration注解的类都将被扫描。</li> <li><code>InternalResourceViewResolver</code> 有助于映射逻辑视图名称,以便在特定的预配置目录下直接查看文件。</li> <li><code>ResourceBundleMessageSource</code> 使用指定的基本名称访问资源包(这里是消息)。</li> <li><code>LocalValidatorFactoryBean</code> 引导一个<code>javax.validation.ValidationFactory</code> ,并通过Spring Validator接口以及JSR-303 Validator接口和<code>ValidatorFactory</code> 接口本身公开它。</li> </ol> <h2>七.Hibernate 配置</h2> 在例子中使用Hibernate配置。 <pre> <code class="language-java">import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.ComponentScans; import org.springframework.context.annotation.Configuration; import org.springframework.orm.hibernate5.HibernateTransactionManager; import org.springframework.orm.hibernate5.LocalSessionFactoryBean; import org.springframework.transaction.annotation.EnableTransactionManagement; import com.howtodoinjava.demo.spring.model.User; @Configuration @EnableTransactionManagement public class HibernateConfig { @Autowired private ApplicationContext context; @Bean public LocalSessionFactoryBean getSessionFactory() { LocalSessionFactoryBean factoryBean = new LocalSessionFactoryBean(); factoryBean.setConfigLocation(context.getResource("classpath:hibernate.cfg.xml")); factoryBean.setAnnotatedClasses(User.class); return factoryBean; } @Bean public HibernateTransactionManager getTransactionManager() { HibernateTransactionManager transactionManager = new HibernateTransactionManager(); transactionManager.setSessionFactory(getSessionFactory().getObject()); return transactionManager; } }</code></pre> <ul> <li><code>LocalSessionFactoryBean</code> 创建一个Hibernate <code>SessionFactory</code>. 这是在Spring应用程序上下文中设置共享Hibernate SessionFactory的常用方法。</li> <li><code>EnableTransactionManagement</code> 支持Spring的注解驱动事务管理功能。</li> <li><code>HibernateTransactionManager</code> 将Hibernate Session从指定的工厂绑定到线程,可能允许每个工厂有一个线程绑定的Session。 此事务管理器适用于使用单个Hibernate</li> <li><code>SessionFactory</code> 进行事务性数据访问的应用程序,但也支持事务内的直接<code>DataSource</code> 访问,即普通JDBC。</li> </ul> <strong>hibernate.cfg.xml:</strong> <pre> <code class="language-xml"><?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.archive.autodetection">class,hbm</property> <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property> <property name="hibernate.show_sql">true</property> <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property> <property name="hibernate.connection.username">sa</property> <property name="hibernate.connection.password"></property> <property name="hibernate.connection.url">jdbc:hsqldb:mem:howtodoinjava</property> <property name="hibernate.hbm2ddl.auto">create</property> <property name="hibernate.c3p0.min_size">5</property> <property name="hibernate.c3p0.max_size">20</property> <property name="hibernate.c3p0.acquire_increment">2</property> <property name="hibernate.c3p0.acquire_increment">1800</property> <property name="hibernate.c3p0.max_statements">150</property> </session-factory> </hibernate-configuration></code></pre> <h2>八.Spring Controller and Path Mappings</h2> 控制器类有两个简单的GET和POST操作映射。 如果输入字段未被验证,则返回相同的表单bean以显示错误消息。 否则返回刷新视图。 <pre> <code class="language-java"> import java.util.Locale; import javax.validation.alid; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.validation.BindingResult; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.PostMapping; import com.howtodoinjava.demo.spring.model.User; import com.howtodoinjava.demo.spring.service.UserService; @Controller public class UserController { @Autowired private UserService userService; @GetMapping("/") public String userForm(Locale locale, Model model) { model.addAttribute("users", userService.list()); return "editUsers"; } @ModelAttribute("user") public User formBackingObject() { return new User(); } @PostMapping("/addUser") public String saveUser(@ModelAttribute("user") @Valid User user, BindingResult result, Model model) { if (result.hasErrors()) { model.addAttribute("users", userService.list()); return "editUsers"; } userService.save(user); return "redirect:/"; } }</code></pre> <h2>九.Service and DAO 层</h2> 服务和DAO层是用@Service和@Repository注释标注的普通服务组件。 @交易注解应用于服务层以支持事务处理。<br /> <br /> <code>UserService</code> and <code>UserServiceImpl</code><br />   <pre> <code class="language-java">public interface UserService { void save(User user); List<User> list(); } @Service public class UserServiceImp implements UserService { @Autowired private UserDao userDao; @Transactional public void save(User user) { userDao.save(user); } @Transactional(readOnly = true) public List<User> list() { return userDao.list(); } }</code></pre> <code>UserDao</code> and <code>UserDaoImp</code><br />   <pre> <code class="language-java">public interface UserDao { void save(User user); List<User> list(); } @Repository public class UserDaoImp implements UserDao { @Autowired private SessionFactory sessionFactory; @Override public void save(User user) { sessionFactory.getCurrentSession().save(user); } @Override public List<User> list() { @SuppressWarnings("unchecked") TypedQuery<User> query = sessionFactory.getCurrentSession().createQuery("from User"); return query.getResultList(); } }</code></pre> User<br />   <pre> <code class="language-java">import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; import javax.validation.constraints.Size; import org.hibernate.validator.constraints.Email; import org.hibernate.validator.constraints.NotEmpty; @Entity @Table(name = "TBL_USERS") public class User { @Id @GeneratedValue @Column(name = "USER_ID") private Long id; @Column(name = "USER_NAME") @Size(max = 20, min = 3, message = "{user.name.invalid}") @NotEmpty(message="Please Enter your name") private String name; @Column(name = "USER_EMAIL", unique = true) @Email(message = "{user.email.invalid}") @NotEmpty(message="Please Enter your email") private String email; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }</code></pre> <h2>十.页面和消息</h2> 最后,使用JSP文件和消息资源包<br /> <br /> editUsers.jsp<br />   <pre> <code class="language-html"><%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Spring5 MVC Hibernate Demo</title> <style type="text/css"> .error { color: red; } table { width: 50%; border-collapse: collapse; border-spacing: 0px; } table td { border: 1px solid #565454; padding: 20px; } </style> </head> <body> <h1>Input Form</h1> <form:form action="addUser" method="post" modelAttribute="user"> <table> <tr> <td>Name</td> <td> <form:input path="name" /> <br /> <form:errors path="name" cssClass="error" /> </td> </tr> <tr> <td>Email</td> <td> <form:input path="email" /> <br /> <form:errors path="email" cssClass="error" /> </td> </tr> <tr> <td colspan="2"><button type="submit">Submit</button></td> </tr> </table> </form:form> <h2>Users List</h2> <table> <tr> <td><strong>Name</strong></td> <td><strong>Email</strong></td> </tr> <c:forEach items="${users}" var="user"> <tr> <td>${user.name}</td> <td>${user.email}</td> </tr> </c:forEach> </table> </body> </html></code></pre> messages.properties<br />   <pre> <code class="language-html">user.name.invalid = Name must be between {2} and {1} characters. user.email.invalid = Please enter valid email address.</code></pre> <h2>十一.演示这个栗子</h2> 让我们使用maven tomcat7插件运行应用程序。 执行maven target:tomcat7:run。<br /> 访问URL: <code>http://localhost:8080</code><br /> Initial Screen<br /> <img alt="Initial Screen" class="img-thumbnail" src="/assist/images/blog/830164ad1b5448dab1aa39d340300c95.png" /><br /> 无效的输入验证<br /> <img alt="无效的输入验证" class="img-thumbnail" src="/assist/images/blog/8ddf9070177c4ce3a00c5dfe4b099950.png" /><br /> 有效的表格提交<br /> <img alt="有效的表格提交" class="img-thumbnail" src="/assist/images/blog/6ef29b6dbc1f4fb7954a3cb9bac38159.png" /><br /> 检查服务器日志: <pre> <code class="language-html">Hibernate: call next value for hibernate_sequence Hibernate: insert into TBL_USERS (USER_EMAIL, USER_NAME, USER_ID) values (?, ?, ?) Hibernate: select user0_.USER_ID as USER_ID1_0_, user0_.USER_EMAIL as USER_EMA2_0_, user0_.USER_NAME as USER_NAM3_0_ from TBL_USERS user0_</code></pre> <a href="https://howtodoinjava.com/wp-content/downloads/spring5-mvc-hibernate-example.zip" rel="external nofollow" target="_blank">Sourcecode Download</a>
  • junit5 新特性与使用

    在Java编程测试中junit5 新特性与使用,Java编程,junit5<h2>一、摘要说明</h2>     本JUnit 5教程讲解了如何使JUnit 5改编成java 8编码风格和其他几个特性。了解JUnit5与JUnit 3或4的不同之处。<br />     JUnit 5是Java应用程序中使用最广泛的测试框架。 很久以来,JUnit一直在做得很好。 在之间,JDK 8在java中引起了非常令人兴奋的功能,最引人注目的是lambda表达式。 JUnit 5旨在适应java 8风格的编码和几个其他功能,这就是为什么需要java 8在JUnit 5中创建和执行测试(尽管可以执行用JUnit 3或JUnit 4编写的测试用于向下兼容)。<br />     本文将讲解以下内容: <ol> <li>架构体系</li> <li>环境搭建</li> <li>注解</li> <li>编写动态测试</li> <li>测试套件</li> <li>断言</li> <li>假设</li> <li>向下兼容性</li> <li>回顾总结</li> </ol> <h2>二、JUnit 5架构体系</h2>   与JUnit 4不同,JUnit 5不再是单个库,而是模块化结构的集合,JUnit 5由三个不同的子项目组成<br /> <br /> JUnit 5 = JUnit Platform + JUnit Jupiter + JUnit Vintage<br /> <br /> <strong>1.JUnit Platform</strong><br /> JUnit平台作为在JVM上启动测试框架的基础。 它还定义了用于开发在平台上运行的测试框架的TestEngine API。 此外,该平台提供了一个控制台启动器,从命令行启动平台,为Gradle和Maven构建插件以及基于JUnit 4的Runner,用于在平台上运行任何TestEngine。<br /> <strong>2.JUnit Jupiter</strong><br /> JUnit Jupiter是用于在JUnit 5中编写测试和扩展的新编程模型和扩展模型的组合.Jupiter子项目提供了一个用于在平台上运行基于Jupiter的测试的TestEngine。<br /> <strong>3.JUnit Vintage</strong><br /> JUnit Vintage提供了一个用于在平台上运行JUnit 3和JUnit 4的测试的TestEngine。 <h2>三、环境搭建</h2> 引入2个最基础的依赖(即Jupiter引擎依赖关系和平台运行程序依赖关系)在您的maven或gradle项目中使用JUnit 5。<br /> <br /> //pom.xml <pre> <code class="language-xml"><dependency>     <groupId>org.junit.jupiter</groupId>     <artifactId>junit-jupiter-engine</artifactId>     <version>${junit.jupiter.version}</version> </dependency> <dependency>     <groupId>org.junit.platform</groupId>     <artifactId>junit-platform-runner</artifactId>     <version>${junit.platform.version}</version>     <scope>test</scope> </dependency></code></pre> //build.gradle <pre> <code>testRuntime("org.junit.jupiter:junit-jupiter-engine:5.0.0-M4") testRuntime("org.junit.platform:junit-platform-runner:1.0.0-M4")</code></pre> 如果有疑问,请参照官方的build.gradle和pom.xml: <h2>四、JUnit 5注解</h2> JUnit 5提供了以下注释来编写测试。<br /> ANNOTATION    DESCRIPTION<br /> @BeforeEach    注释方法将在测试类中的每个测试方法之前运行。<br /> @AfterEach    注释方法将在测试类中的每个测试方法之后运行。<br /> @BeforeAll    注释方法将在测试类中的所有测试方法之前运行。 此方法必须是静态的。<br /> @AfterAll    注释方法将在测试类中的所有测试方法之后运行。 此方法必须是静态的。<br /> @Test    它用于将方法标记为junit测试<br /> @DisplayName    用于为测试类或测试方法提供任何自定义显示名称<br /> @Disable    它用于禁用或忽略来自测试套件的测试类或方法。<br /> @Nested    用于创建嵌套测试类<br /> @Tag    使用用于测试发现和过滤的标签来标记测试方法或测试类<br /> @TestFactory    标记方法是动态测试的测试工厂 <h2>五、使用JUnit 5中编写动态测试</h2> JUnit 4和JUnit 5在测试写作风格中没有太大变化。 这里是他们的生命周期方法的样本测试。 <pre> <code class="language-java">import org.junit.jupiter.api.AfterAll; import org.junit.jupiter.api.AfterEach; import org.junit.jupiter.api.Assertions; import org.junit.jupiter.api.BeforeAll; import org.junit.jupiter.api.BeforeEach; import org.junit.jupiter.api.Disabled; import org.junit.jupiter.api.Tag; import org.junit.jupiter.api.Test; import com.howtodoinjava.junit5.examples.Calculator; public class AppTest { @BeforeAll static void setup(){ System.out.println("@BeforeAll executed"); } @BeforeEach void setupThis(){ System.out.println("@BeforeEach executed"); } @Tag("DEV") @Test void testCalcOne() { System.out.println("======TEST ONE EXECUTED======="); Assertions.assertEquals( 4 , Calculator.add(2, 2)); } @Tag("PROD") @Disabled @Test void testCalcTwo() { System.out.println("======TEST TWO EXECUTED======="); Assertions.assertEquals( 6 , Calculator.add(2, 4)); } @AfterEach void tearThis(){ System.out.println("@AfterEach executed"); } @AfterAll static void tear(){ System.out.println("@AfterAll executed"); } } </code></pre> <h2>六、测试套件</h2> 使用JUnit 5测试套件,您可以将测试扩展到多个测试类和不同的软件包。 JUnit 5提供了两个注解:@SelectPackages和@SelectClasses来创建测试套件。<br /> 要执行该套件,您将使用@RunWith(JUnitPlatform.class)。 <pre> <code class="language-java">​​​​​​​@RunWith(JUnitPlatform.class) @SelectPackages("com.howtodoinjava.junit5.examples") public class JUnit5TestSuiteExample  { }</code></pre> <br /> 此外,您可以使用以下注解来过滤测试包,类甚至测试方法。<br /> 1.@IncludePackages 和 @ExcludePackages 过滤测试包<br /> 2.@IncludeClassNamePatterns 和 @ExcludeClassNamePatterns 过滤测试类<br /> 3.@IncludeTags 和 @ExcludeTags 过滤测试方法 <pre> <code class="language-java">@RunWith(JUnitPlatform.class) @SelectPackages("com.howtodoinjava.junit5.examples") @IncludePackages("com.howtodoinjava.junit5.examples.packageC") @ExcludeTags("PROD") public class JUnit5TestSuiteExample  { }</code></pre> <h2>七、Junit5中的断言</h2>   断言有助于使用测试用例的实际输出验证预期输出。 为了保持简单,所有JUnit Jupiter断言是org.junit.jupiter.Assertions类中的静态方法,例如 assertEquals(),assertNotEquals()。 <pre> <code class="language-java">void testCase() { //Test will pass Assertions.assertNotEquals(3, Calculator.add(2, 2)); //Test will fail Assertions.assertNotEquals(4, Calculator.add(2, 2), "Calculator.add(2, 2) test failed"); //Test will fail Supplier<String> messageSupplier = ()-> "Calculator.add(2, 2) test failed"; Assertions.assertNotEquals(4, Calculator.add(2, 2), messageSupplier); }</code></pre> <h2>八、假设</h2>     Assumptions类提供了静态方法来支持基于假设的条件测试执行。 失败的假设导致测试被中止。 无论何时继续执行给定的测试方法没有意义,通常使用假设。 在测试报告中,这些测试将被标记为已通过。<br />     JUnit jupiter Assumptions类有两个这样的方法:putsFalse(),putsTrue()。 <pre> <code class="language-java">public class AppTest { @Test void testOnDev() { System.setProperty("ENV", "DEV"); Assumptions.assumeTrue("DEV".equals(System.getProperty("ENV")), AppTest::message); } @Test void testOnProd() { System.setProperty("ENV", "PROD"); Assumptions.assumeFalse("DEV".equals(System.getProperty("ENV"))); } private static String message () { return "TEST Execution Failed :: "; } }</code></pre> <h2>九、兼容性</h2> JUnit 3或JUnit 4的向下兼容性<br /> <br /> JUnit 4已经在这里了很长时间,并且有许多测试以junit 4写成.JUnit Jupiter还需要支持这些测试。 为此,开发了JUnit Vintage子项目。<br /> <br /> JUnit Vintage提供了一个TestEngine实现,用于在JUnit 5平台上运行基于JUnit 3和JUnit 4的测试。 <h2>十、总结</h2>     JUnit 5还在开发中。 看起来如此令人兴奋,功能丰富。 而现在它被第三方工具和API扩展开放。 作为测试作者,您可能不会觉得有什么不同,但是当您要扩展或尝试开发任何IDE插件时,您会赞美它。<br /> <br />     作为开发人员,您还可以考虑将测试模板添加到eclipse IDE中以提高开发速度。