【SQL篇章】【SQL语句梳理
:–基于MySQL5.6】【已梳理:DATABASE/EVENTS】【会坚持完善】

 

1.安装过程没有什么说头

 

Preface

2.下载并安装SSMS(SQLServer Management Studio),目前已更新到2017.1

目录:

 

3.使用SSMS登陆数据库

1. Data Definition Statements:

    In my previous two blogs,we have known
about the tool of backing up MySQL db.I’m gonna use another tool named
“MySQL Data Dumper” to do some test,here we go.

第一次选择Windows 身份验证,可免密码直接登陆。

  1.1 create database, alter database, show databases

 

 图片 1

  1.2 create event, alter event, show events

Introduce

 

 

 

 

 

    There’s a third-party tool called mydumper
which is similar with MySQL official backup tool mysqldump and
identically backs up logically.The latest version of  mydumper
is 0.9.3,Here’s the website link:**

3.登陆后,创建自己的数据库

1. Data Definition
Statements:

 

右键数据库文件夹,点击新建数据库。

1.1

  1 ###Download & Install mydumper.###
  2 [root@zlm1 16:10:55 ~]
  3 #wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
  4 --2018-06-17 16:13:27--  https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
  5 Resolving launchpad.net (launchpad.net)... 91.189.89.223, 91.189.89.222
  6 Connecting to launchpad.net (launchpad.net)|91.189.89.223|:443... connected.
  7 HTTP request sent, awaiting response... 303 See Other
  8 Location: https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz [following]
  9 --2018-06-17 16:13:38--  https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz
 10 Resolving launchpadlibrarian.net (launchpadlibrarian.net)... 91.189.89.229, 91.189.89.228
 11 Connecting to launchpadlibrarian.net (launchpadlibrarian.net)|91.189.89.229|:443... connected.
 12 HTTP request sent, awaiting response... 200 OK
 13 Length: 44463 (43K) [application/x-tar]
 14 Saving to: ‘mydumper-0.9.1.tar.gz’
 15 
 16 100%[===========================================================================================================>] 44,463      67.6KB/s   in 0.6s   
 17 
 18 2018-06-17 16:13:50 (67.6 KB/s) - ‘mydumper-0.9.1.tar.gz’ saved [44463/44463]
 19 
 20 
 21 [root@zlm1 16:14:40 ~]
 22 #ls -l
 23 total 99860
 24 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
 25 -rw-r--r--   1 root root    44463 Nov  6  2015 mydumper-0.9.1.tar.gz
 26 drwxrwxrwx   6 root root     4096 Jun  8 17:31 mysqlbinlog_flashback-master
 27 -rwxr-xr-x   1 root root       58 Jun  4 16:01 mysqld.sh
 28 -rwxr-xr-x   1 root root       40 Jun  1 16:13 mysql.sh
 29 -rw-r--r--   1 root root  7839980 Jul 21  2016 percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
 30 drwxrwxrwx   6 root root     4096 Jun  8 16:59 pip-10.0.1
 31 -rwxr-xr-x   1 root root  4720640 Jun  8 16:57 pip-10.0.1.tar
 32 drwxr-xr-x   6 root root     4096 Jun  8 17:25 PyMySQL-0.8.1
 33 -rwxr-xr-x   1 root root   358400 Jun  8 17:08 PyMySQL-0.8.1.tar
 34 drwxrwxr-x  18 root root     4096 Jun  8 16:38 Python-2.6.9
 35 -rwxr-xr-x   1 root root 59238400 Jun  8 16:28 Python-2.6.9.tar
 36 -rw-r--r--   1 root root      181 Jun  1 20:18 recover.sh
 37 -rw-r--r--   1 root root        0 May 30 20:33 rename_tb.sql
 38 drwxrwxrwx   9 root root     4096 Jun  8 17:22 setuptools-39.2.0
 39 
 40 [root@zlm1 16:15:06 ~]
 41 #gzip -d mydumper-0.9.1.tar.gz 
 42 
 43 [root@zlm1 16:15:26 ~]
 44 #tar -xf mydumper-0.9.1.tar
 45 
 46 [root@zlm1 16:16:41 ~]
 47 #cd mydumper-0.9.1
 48 
 49 [root@zlm1 16:16:47 ~/mydumper-0.9.1]
 50 #ls -l
 51 total 168
 52 -rw-r--r-- 1 root root  8171 Nov  6  2015 binlog.c
 53 -rw-r--r-- 1 root root  1244 Nov  6  2015 binlog.h
 54 drwxr-xr-x 3 root root    20 Jun 17 16:15 cmake
 55 -rw-r--r-- 1 root root  2339 Nov  6  2015 CMakeLists.txt
 56 -rw-r--r-- 1 root root  2081 Nov  6  2015 common.h
 57 -rw-r--r-- 1 root root   101 Nov  6  2015 config.h.in
 58 drwxr-xr-x 4 root root  4096 Jun 17 16:15 docs
 59 -rw-r--r-- 1 root root  3614 Nov  6  2015 g_unix_signal.c
 60 -rw-r--r-- 1 root root   339 Nov  6  2015 g_unix_signal.h
 61 -rw-r--r-- 1 root root 92941 Nov  6  2015 mydumper.c
 62 -rw-r--r-- 1 root root  2169 Nov  6  2015 mydumper.h
 63 -rw-r--r-- 1 root root 16630 Nov  6  2015 myloader.c
 64 -rw-r--r-- 1 root root  1284 Nov  6  2015 myloader.h
 65 -rw-r--r-- 1 root root  2244 Nov  6  2015 README
 66 -rw-r--r-- 1 root root  2021 Nov  6  2015 server_detect.c
 67 -rw-r--r-- 1 root root  1116 Nov  6  2015 server_detect.h
 68 
 69 [root@zlm1 16:17:22 ~/mydumper-0.9.1]
 70 #cmake .
 71 -bash: cmake: command not found -- There's not cmake package in my system,install cmake first.
 72 
 73 [root@zlm1 16:28:50 ~/mydumper-0.9.1]
 74 #yum install cmake
 75 -- Omitted.
 76 
 77 [root@zlm1 16:29:33 ~/mydumper-0.9.1]
 78 #cmake .
 79 -- The C compiler identification is GNU 4.8.3
 80 -- The CXX compiler identification is GNU 4.8.3
 81 -- Check for working C compiler: /usr/bin/cc
 82 -- Check for working C compiler: /usr/bin/cc -- works
 83 -- Detecting C compiler ABI info
 84 -- Detecting C compiler ABI info - done
 85 -- Check for working CXX compiler: /usr/bin/c++
 86 -- Check for working CXX compiler: /usr/bin/c++ -- works
 87 -- Detecting CXX compiler ABI info
 88 -- Detecting CXX compiler ABI info - done
 89 -- Using mysql-config: /usr/local/mysql/bin/mysql_config
 90 -- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
 91 -- Found ZLIB: /usr/lib64/libz.so (found version "1.2.7") 
 92 -- Found PkgConfig: /usr/bin/pkg-config (found version "0.27.1") 
 93 -- checking for one of the modules 'glib-2.0'
 94 CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:363 (message):
 95   None of the required 'glib-2.0' found
 96 Call Stack (most recent call first):
 97   cmake/modules/FindGLIB2.cmake:10 (pkg_search_module)
 98   CMakeLists.txt:10 (find_package)
 99 
100 
101 -- checking for one of the modules 'gthread-2.0'
102 CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:363 (message):
103   None of the required 'gthread-2.0' found
104 Call Stack (most recent call first):
105   cmake/modules/FindGLIB2.cmake:11 (pkg_search_module)
106   CMakeLists.txt:10 (find_package)
107 
108 
109 -- checking for module 'libpcre'
110 --   found libpcre, version 8.32
111 -- Found PCRE: /usr/include  
112 
113 CMake Warning at docs/CMakeLists.txt:9 (message):
114   Unable to find Sphinx documentation generator
115 
116 
117 -- ------------------------------------------------
118 -- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
119 -- CMAKE_INSTALL_PREFIX = /usr/local
120 -- BUILD_DOCS = ON
121 -- WITH_BINLOG = OFF
122 -- RUN_CPPCHECK = OFF
123 -- Change a values with: cmake -D<Variable>=<Value>
124 -- ------------------------------------------------
125 -- 
126 CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
127 Please set them or make sure they are set and tested correctly in the CMake files:
128 GLIB2_LIBRARIES (ADVANCED)
129     linked by target "mydumper" in directory /root/mydumper-0.9.1
130     linked by target "myloader" in directory /root/mydumper-0.9.1
131 GTHREAD2_LIBRARIES (ADVANCED)
132     linked by target "mydumper" in directory /root/mydumper-0.9.1
133     linked by target "myloader" in directory /root/mydumper-0.9.1
134 
135 -- Configuring incomplete, errors occurred!
136 See also "/root/mydumper-0.9.1/CMakeFiles/CMakeOutput.log".
137 
138 [root@zlm1 16:41:25 ~/mydumper-0.9.1]
139 #yum install glib2-devel -- Install glib2-devel package to solve the problem above.
140 --Omitted.
141 
142 [root@zlm1 16:49:17 ~/mydumper-0.9.1]
143 #cmake .
144 -- Using mysql-config: /usr/local/mysql/bin/mysql_config
145 -- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
146 -- checking for one of the modules 'glib-2.0'
147 -- checking for one of the modules 'gthread-2.0'
148 
149 CMake Warning at docs/CMakeLists.txt:9 (message):
150   Unable to find Sphinx documentation generator
151 
152 
153 -- ------------------------------------------------
154 -- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
155 -- CMAKE_INSTALL_PREFIX = /usr/local
156 -- BUILD_DOCS = ON
157 -- WITH_BINLOG = OFF
158 -- RUN_CPPCHECK = OFF
159 -- Change a values with: cmake -D<Variable>=<Value>
160 -- ------------------------------------------------
161 -- 
162 -- Configuring done
163 -- Generating done
164 -- Build files have been written to: /root/mydumper-0.9.1
165 
166 [root@zlm1 16:49:21 ~/mydumper-0.9.1]
167 #make
168 Scanning dependencies of target mydumper
169 [ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
170 [ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
171 [ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
172 Linking C executable mydumper
173 [ 75%] Built target mydumper
174 Scanning dependencies of target myloader
175 [100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
176 Linking C executable myloader
177 [100%] Built target myloader
178 
179 [root@zlm1 16:50:31 ~/mydumper-0.9.1]
180 #ls -l | grep my
181 -rwxr-xr-x 1 root root 241428 Jun 17 16:50 mydumper -- This can be used to backup db.
182 -rw-r--r-- 1 root root  92941 Nov  6  2015 mydumper.c
183 -rw-r--r-- 1 root root   2169 Nov  6  2015 mydumper.h
184 -rwxr-xr-x 1 root root  63401 Jun 17 16:50 myloader -- This can be used to restore db.
185 -rw-r--r-- 1 root root  16630 Nov  6  2015 myloader.c
186 -rw-r--r-- 1 root root   1284 Nov  6  2015 myloader.hss
187 
188 [root@zlm1 16:52:33 ~/mydumper-0.9.1]
189 #mydumper --help
190 -bash: mydumper: command not found
191 
192 ###Copy
193 [root@zlm1 16:57:11 ~/mydumper-0.9.1]
194 #cp mydumper /usr/bin
195 
196 [root@zlm1 16:58:27 ~/mydumper-0.9.1]
197 #cp myloader /usr/bin
198 
199 [root@zlm1 16:58:33 ~/mydumper-0.9.1]
200 #mydumper --help -- There's not option to dump binlogs in new version,you cannot find parameter "-b" anymore.
201 Usage:
202   mydumper [OPTION?] multi-threaded MySQL dumping
203 
204 Help Options:
205   -?, --help                  Show help options
206 
207 Application Options:
208   -B, --database              Database to dump
209   -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
210   -o, --outputdir             Directory to output files to
211   -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000
212   -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
213   -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB
214   -c, --compress              Compress output files
215   -e, --build-empty-files     Build dump files even if no data available from table
216   -x, --regex                 Regular expression for 'db.table' matching
217   -i, --ignore-engines        Comma delimited list of storage engines to ignore
218   -m, --no-schemas            Do not dump table schemas with the data
219   -d, --no-data               Do not dump table data
220   -G, --triggers              Dump triggers
221   -E, --events                Dump events
222   -R, --routines              Dump stored procedures and functions
223   -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
224   --less-locking              Minimize locking time on InnoDB tables.
225   -l, --long-query-guard      Set long query timer in seconds, default 60
226   -K, --kill-long-queries     Kill long running queries (instead of aborting)
227   -D, --daemon                Enable daemon mode
228   -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60
229   -L, --logfile               Log file name to use, by default stdout is used
230   --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
231   --skip-tz-utc               
232   --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
233   --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
234   --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
235   -U, --updated-since         Use Update_time to dump only tables updated in the last U days
236   --trx-consistency-only      Transactional consistency only
237   -h, --host                  The host to connect to
238   -u, --user                  Username with privileges to run the dump
239   -p, --password              User password
240   -P, --port                  TCP/IP port to connect to
241   -S, --socket                UNIX domain socket file to use for connection
242   -t, --threads               Number of threads to use, default 4
243   -C, --compress-protocol     Use compression on the MySQL connection
244   -V, --version               Show the program version and exit
245   -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
246 
247 ###create a big MyISAM table.###
248 root@localhost:mysql3306.sock [zlm]06:34:15>create table test_myisam(
249     -> id int primary key
250     -> ) engine=myisam;
251 Query OK, 0 rows affected (0.00 sec)
252 
253 root@localhost:mysql3306.sock [zlm]06:34:53>delimiter $$
254 root@localhost:mysql3306.sock [zlm]06:35:03>create procedure pro_insert (count int)
255     -> begin
256     -> declare i int unsigned default 0;
257     -> start transaction;
258     -> while i < count do
259     -> insert into test_myisam(id) values(i);
260     -> set i=i+1;
261     -> end while;
262     -> commit;
263     -> end;
264     -> $$
265 Query OK, 0 rows affected (0.00 sec)
266 
267 root@localhost:mysql3306.sock [zlm]06:35:03>delimiter ;
268 root@localhost:mysql3306.sock [zlm]06:35:04>call pro_insert(10000000);
269 Query OK, 0 rows affected (11 min 37.72 sec)
270 
271 root@localhost:mysql3306.sock [zlm]06:46:57>select count(*) from test_myisam;
272 +----------+
273 | count(*) |
274 +----------+
275 | 10000000 |
276 +----------+
277 1 row in set (0.03 sec)
278 
279 root@localhost:mysql3306.sock [zlm]06:48:55>show tables;
280 +----------------+
281 | Tables_in_zlm  |
282 +----------------+
283 | semi_sync_test |
284 | t1             |
285 | t2             |
286 | t3             |
287 | test           |
288 | test_flashbk   |
289 | test_myisam    |
290 +----------------+
291 7 rows in set (0.06 sec)
292 
293 root@localhost:mysql3306.sock [zlm]06:51:25>show create table test;
294 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
295 | Table | Create Table                                                                                                                                                                                  |
296 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
297 | test  | CREATE TABLE `test` (
298   `id` bigint(20) NOT NULL AUTO_INCREMENT,
299   `name` varchar(20) NOT NULL DEFAULT '',
300   PRIMARY KEY (`id`)
301 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 |
302 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
303 1 row in set (0.00 sec)
304 
305 ###Generate a backup by 2 threads.###
306 [root@zlm1 18:57:45 ~/mydumper-0.9.1]
307 #mydumper -B zlm -T test,test_myisam -u root -p Passw0rd -h localhost -t 2 -o /data/backup -- -t specify threads(default 4) in all.
308 
309 [root@zlm1 18:58:47 ~/mydumper-0.9.1]
310 
311 ###Check the output file.###
312 [root@zlm1 18:58:57 ~/mydumper-0.9.1]
313 #ls -l /data/backup
314 total 108408
315 drwxr-x--- 7 root root      4096 Jun 16 11:29 2018-06-16_11-28-59
316 -rw-r--r-- 1 root root       188 Jun 17 18:58 metadata -- It contains time,binlog file & position,GTID informations.
317 -rw-r--r-- 1 root root        62 Jun 17 18:58 zlm-schema-create.sql -- It contains database structure.
318 -rw-r--r-- 1 root root       180 Jun 17 18:58 zlm.test_myisam-schema.sql  -- It contains table structure.
319 -rw-r--r-- 1 root root 108892589 Jun 17 18:58 zlm.test_myisam.sql -- It contains data of MyISAM table "test_myisam".
320 -rw-r--r-- 1 root root       258 Jun 17 18:58 zlm.test-schema.sql -- It contains table structure.
321 -rw-r--r-- 1 root root   2089075 Jun 17 18:58 zlm.test.sql -- It contains data of innodb table "test".
322 
323 ###Check the general log for detail of backup.###
324 [root@zlm1 18:58:26 /data/mysql/mysql3306/data]
325 #cat zlm1.log
326 
327 2018-06-17T16:58:40.634569Z       29 Connect    root@localhost on zlm using Socket
328 2018-06-17T16:58:40.634595Z       29 Query    SET SESSION wait_timeout = 2147483
329 2018-06-17T16:58:40.634670Z       29 Query    SET SESSION net_write_timeout = 2147483
330 2018-06-17T16:58:40.634819Z       29 Query    SHOW PROCESSLIST
331 2018-06-17T16:58:40.634902Z       29 Query    FLUSH TABLES WITH READ LOCK -- Begin to generate FTWRL in order to have consistent backup.
332 2018-06-17T16:58:40.634997Z       29 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ -- create consistent snapshot.
333 2018-06-17T16:58:40.635072Z       29 Query    /*!40101 SET NAMES binary*/
334 2018-06-17T16:58:40.635125Z       29 Query    SHOW MASTER STATUS
335 2018-06-17T16:58:40.635205Z       29 Query    SHOW SLAVE STATUS
336 2018-06-17T16:58:40.636261Z       30 Connect    root@localhost on  using Socket -- Create sub thread #1 in backing up.
337 2018-06-17T16:58:40.636392Z       30 Query    SET SESSION wait_timeout = 2147483
338 2018-06-17T16:58:40.636443Z       30 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
339 2018-06-17T16:58:40.636479Z       30 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
340 2018-06-17T16:58:40.636527Z       30 Query    /*!40103 SET TIME_ZONE='+00:00' */
341 2018-06-17T16:58:40.636570Z       30 Query    /*!40101 SET NAMES binary*/
342 2018-06-17T16:58:40.636913Z       31 Connect    root@localhost on  using Socket -- Create sub thread #2 in backing up.
343 2018-06-17T16:58:40.636962Z       31 Query    SET SESSION wait_timeout = 2147483
344 2018-06-17T16:58:40.637005Z       31 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
345 2018-06-17T16:58:40.637039Z       31 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
346 2018-06-17T16:58:40.637084Z       31 Query    /*!40103 SET TIME_ZONE='+00:00' */
347 2018-06-17T16:58:40.637123Z       31 Query    /*!40101 SET NAMES binary*/
348 2018-06-17T16:58:40.637178Z       29 Init DB    zlm
349 2018-06-17T16:58:40.637219Z       29 Query    SHOW TABLE STATUS
350 2018-06-17T16:58:40.772453Z       29 Query    SHOW CREATE DATABASE `zlm`
351 2018-06-17T16:58:40.772650Z       30 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test_myisam`
352 2018-06-17T16:58:40.772927Z       31 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test`
353 2018-06-17T16:58:40.848929Z       31 Query    SHOW CREATE TABLE `zlm`.`test` -- Get the table structure of "test".
354 2018-06-17T16:58:40.961590Z       31 Query    SHOW CREATE TABLE `zlm`.`test_myisam` -- Get the table structure of "test_myisam".
355 2018-06-17T16:58:47.022712Z       29 Query    UNLOCK TABLES /* FTWRL */ -- Release table locks after get structure of tables.
356 2018-06-17T16:58:47.022724Z       31 Quit    
357 2018-06-17T16:58:47.022812Z       29 Quit    
358 2018-06-17T16:58:47.024460Z       30 Quit
359 
360 ###Generate a backup by 4 threads.###
361 [root@zlm1 19:05:10 ~/mydumper-0.9.1]
362 #mydumper -B zlm -T test,test_myisam -u root -p Passw0rd -h localhost -t 4 -o /data/backup
363 
364 [root@zlm1 19:27:06 ~/mydumper-0.9.1]
365 #
366 
367 ###Check the general log for detail of backup.###
368 [root@zlm1 19:26:37 /data/mysql/mysql3306/data]
369 #cat zlm1.log
370 
371 2018-06-17T17:27:01.654622Z       32 Connect    root@localhost on zlm using Socket
372 2018-06-17T17:27:01.654864Z       32 Query    SET SESSION wait_timeout = 2147483
373 2018-06-17T17:27:01.654922Z       32 Query    SET SESSION net_write_timeout = 2147483
374 2018-06-17T17:27:01.655009Z       32 Query    SHOW PROCESSLIST
375 2018-06-17T17:27:01.655072Z       32 Query    FLUSH TABLES WITH READ LOCK
376 2018-06-17T17:27:01.656485Z       32 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
377 2018-06-17T17:27:01.656577Z       32 Query    /*!40101 SET NAMES binary*/
378 2018-06-17T17:27:01.656744Z       32 Query    SHOW MASTER STATUS
379 2018-06-17T17:27:01.656832Z       32 Query    SHOW SLAVE STATUS
380 2018-06-17T17:27:01.657303Z       33 Connect    root@localhost on  using Socket -- Create sub thread #1 in backing up.
381 2018-06-17T17:27:01.657367Z       33 Query    SET SESSION wait_timeout = 2147483
382 2018-06-17T17:27:01.657416Z       33 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
383 2018-06-17T17:27:01.657453Z       33 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
384 2018-06-17T17:27:01.657503Z       33 Query    /*!40103 SET TIME_ZONE='+00:00' */
385 2018-06-17T17:27:01.657547Z       33 Query    /*!40101 SET NAMES binary*/
386 2018-06-17T17:27:01.658353Z       34 Connect    root@localhost on  using Socket -- Create sub thread #2 in backing up.
387 2018-06-17T17:27:01.658438Z       34 Query    SET SESSION wait_timeout = 2147483
388 2018-06-17T17:27:01.658485Z       34 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
389 2018-06-17T17:27:01.658568Z       34 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
390 2018-06-17T17:27:01.658631Z       34 Query    /*!40103 SET TIME_ZONE='+00:00' */
391 2018-06-17T17:27:01.658689Z       34 Query    /*!40101 SET NAMES binary*/
392 2018-06-17T17:27:01.659442Z       35 Connect    root@localhost on  using Socket -- Create sub thread #3 in backing up.
393 2018-06-17T17:27:01.659541Z       35 Query    SET SESSION wait_timeout = 2147483
394 2018-06-17T17:27:01.659580Z       35 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
395 2018-06-17T17:27:01.659581Z       35 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
396 2018-06-17T17:27:01.659581Z       35 Query    /*!40103 SET TIME_ZONE='+00:00' */
397 2018-06-17T17:27:01.659614Z       35 Query    /*!40101 SET NAMES binary*/
398 2018-06-17T17:27:01.659992Z       36 Connect    root@localhost on  using Socket -- Create sub thread #4 in backing up.
399 2018-06-17T17:27:01.660057Z       36 Query    SET SESSION wait_timeout = 2147483
400 2018-06-17T17:27:01.660106Z       36 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
401 2018-06-17T17:27:01.660144Z       36 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
402 2018-06-17T17:27:01.660193Z       36 Query    /*!40103 SET TIME_ZONE='+00:00' */
403 2018-06-17T17:27:01.660236Z       36 Query    /*!40101 SET NAMES binary*/
404 2018-06-17T17:27:01.660801Z       32 Init DB    zlm
405 2018-06-17T17:27:01.661053Z       32 Query    SHOW TABLE STATUS
406 2018-06-17T17:27:01.662581Z       32 Query    SHOW CREATE DATABASE `zlm`
407 2018-06-17T17:27:01.662906Z       33 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test_myisam`
408 2018-06-17T17:27:01.669514Z       34 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test`
409 2018-06-17T17:27:01.674024Z       35 Query    SHOW CREATE TABLE `zlm`.`test`
410 2018-06-17T17:27:01.681205Z       35 Query    SHOW CREATE TABLE `zlm`.`test_myisam`
411 2018-06-17T17:27:06.548006Z       32 Query    UNLOCK TABLES /* FTWRL */
412 2018-06-17T17:27:06.548426Z       35 Quit    
413 2018-06-17T17:27:06.548509Z       36 Quit    
414 2018-06-17T17:27:06.548552Z       34 Quit    
415 2018-06-17T17:27:06.548619Z       32 Quit    
416 2018-06-17T17:27:06.549291Z       33 Quit

 图片 2

CREATE
DATABASE

 

 

格式:

Summary

4.作为开发测试,只使用默认的配置,简单填写后直接确定

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] create_specification :
  [DEFAULT] CHARACTER SET [=] charset_name
  [DEFAULT] COLLATE [=] collation_name
  • mydump
    is a logical backup tool like mysqldump,but more
    efficient.
  • mydump support mutiple thread
    backup,which can short your backup time especially when backing up
    big tables.
  • When backing up operation begins
    there also a FTWRL to make a consistent backup.

 图片 3

  

 

 数据库名是连接字符串中的Initial Catalog

事例 :

5.为新建的数据库添加远程登陆用户名和密码

CREATE DATABASE db2;
CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */

在安全性文件夹中的登陆名文件夹上右键,新建登陆名。

 

 图片 4

查看:

 

SHOW DATABASES;

6.配置登陆名

 

此处需要配置多项,首先是User
ID与密码,可勾选强制实施密码策略、强制密码过期和下次登陆时必须修改密码,由于是新建管理员,所以并不设密码过期。

ALTER
DATABASE

 图片 5

格式:

 

ALTER {DATABASE | SCHEMA} [db_name]
  alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
  UPGRADE DATA DIRECTORY NAME

alter_specification:
  [DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name

7.勾选服务器角色

  

一般会勾选public,如果是管理员,还要勾选sysadmin

 

 图片 6

事例操作:

 

<1>
–>db1:utf8

8.勾选用户映射

ALTER DATABASE db1 CHARACTER SET = utf8;

即确定用户是映射自哪个数据库的。由于我是管理员,所以角色成员勾选的比较多,别忘了public角色成员。

 

 图片 7

查看:

 

mysql> SELECT * FROM information_schema.`SCHEMATA` WHERE schema_name='db1';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | db1         | utf8                       | utf8_general_ci        | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.04 sec)

9.安全对象使用默认,状态设置中选择授予和启用。

  

 图片 8

<2>
–>db1:utf8–>latin1

 

ALTER DATABASE db1 CHARACTER SET = latin1;

10.断开旧连接,使用新建的用户名和密码登陆,测试连接是否成功。

  

 图片 9

查看:

 

mysql> SELECT * FROM information_schema.`SCHEMATA` WHERE schema_name='db1';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | db1         | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

服务器名称是连接字符串中的DataSource,远程登陆的话,身份验证选择SqlServer身份验证,这样可以填写账号和密码。默认账号为SA,默认密码为安装时填写的密码。但此处我们填写刚刚新建的用户和密码。

  

11.A若登陆测试成功,则下一步应该配置防火墙,

<3>
–创建表t5:  <db1:latin1>

11.B否则使用Sql Server Configuration Manager检查本机网络配置

CREATE TABLE t5(id int);

 图片 10

  查看所建立表的字符集

 

mysql> SHOW CREATE TABLE db1.t5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

A1.打开Windows Server
服务管理器,选择本地服务器,修改防火墙高级设置的入站规则。

  测试发现:

 图片 11

  1.db的character改变后,collation随之改变。
  2.db中旧表character不会改变。新建表character默认为与当前db相同。

 

  

A2.新建程序入站规则

 

目的是为开放SQL Server的动态连接端口,文档详见

SHOW
DATABASES;

格式:

SHOW
{DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr]

程序路径为
$“{数据库实例安装路径}\MSSQL{版本号}.{数据库实例名}\MSSQL\Binn\Sqlservr.exe”,若分不清,直接搜索Sqlservr.exe。注意拼写Sqlservr.exe。

 

 图片 12

DROP
DATABASE;

 

格式:

A3.新建UDP开放端口1434

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

目的是使用SQL Server Browser来动态分配端口。

 

 图片 13

  1.IF
EXISTS : DB不存在,不会报错

 

  2.DB
drop掉,对应的物理目录也会删除。但是DB对应目录下有其它文件,无法执行drop
DB的操作,报错

12.使用局域网内另一台计算机进行测试

mysql> drop database wb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './wb/', errno: 17)

首先必须能够Ping通服务器主机,若不能,打开防火墙-入站规则-文件和打印共享(回显请求),ipv4或ipv6看情况。

  

 图片 14

删除非数据库目录或文件:

 

mysql> drop database wb;
Query OK, 0 rows affected (0.00 sec)

13.方便的话,使用Visual Studio
2017的数据库连接服务进行快速测试和快速生成连接字符串。也可使用程序进行测试。

  

此处填写的配置与建立数据库时一致。

1.2

 图片 15

CREATE
EVENT

 

格式:

可将生成的字符串用代码再一次进行验证。

CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;

schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

 

  

图片 16图片 17

注意事项:

 1     public class SqlServerHelper
 2     {
 3         private static readonly string connStr = @"Data Source=WIN-3JD7GUIRB36\VMSQLSERVER;Initial Catalog=VM719DataBase;Persist Security Info=True;User ID=VM719MANAGER;PWD=test1234;Connect Timeout=5";
 4         private static SqlConnection sqlConn;
 5         private static SqlDataAdapter sqlAdapter;
 6         private static SqlCommand sqlCommand;
 7 
 8         public static DataSet GetTestTableData()
 9         {
10             try
11             {
12                 using (sqlConn = new SqlConnection(connStr))
13                 {
14                     sqlConn.Open();
15                 }
16             }
17             catch (Exception e)
18             {
19                 Console.WriteLine(e);
20                 return null;
21             }
22             return null;
23         }
24     }
  • 创建event需要:event,super权限,建好之后,必须是enable状态。
  • ON
    SCHEDULE : 决定什么时间或者多长时间,时间执行一次
  • DO
    :包含被event执行的SQL语句

测试用代码

默认 EVENT
支持是没有启动的,可以通过下面的命令来查看状态:

 

Select @@event_scheduler;

14.测试连接

  

若无法连接成功,先关闭Windows
Server中的防火墙进行测试,排除是否为防火墙未配置正确。否则从B1开始检查。

如果返回 OFF
,则需要执行下面的命令启动:

B1.远程使用用户和密码方式登录实际为TCP,所以应该启用TCP/IP协议。

SET GLOBAL event_scheduler = ON;

 图片 18

  

 

好了,上面虽然启动了
EVENT ,但是每次重启 mysql 之后 EVENT
并没有自动启动,那么如何让它自动启动呢?

Named
Pipes,命名管道是使用$IPSec服务,在很多计算机上为了安全是关闭的,且只能在局域网中对数据库进行连接。不过连接速度较TCP/IP更快。

方法一:找到当前使用的
.cnf 文件

B2.检查连接字符串是否与别名不一致。

[mysqld] 
event_scheduler=1

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注