Jelikož do portfolia společnosti Oracle patří nyní i databáze MySQL, rozhodl jsem se na ni podívat a zjistit, jak se s ní pracuje.
Dost často se mě totiž lidé ptají, jaké jsou mezi oběma databázemi rozdíly. Doposud jsem neměl potřebu s databází MySQL pracovat, ale kdo ví, jak to bude v budoucnu, proto je potřeba se připravit.
Získaní zdrojáků
Buď přes Oracle OTN (který vás stejně jenom přesměruje) a nebo přímo na stránky MySQL.
Minimálně budete potřebovat server a clienta, ale lze stáhnout další podpůrné balíky. Oproti Oracle db to jsou maličké RPM balíky.
[root@testsrv01 tsolar]# ls -ltr
total 119320
-rw-r–r– 1 root root 49731591 Sep 7 23:48 MySQL-server-5.5.15-1.linux2.6.i386.rpm
-rw-r–r– 1 root root 1905902 Sep 7 23:49 MySQL-shared-5.5.15-1.linux2.6.i386.rpm
-rw-r–r– 1 root root 16966399 Sep 7 23:49 MySQL-client-5.5.15-1.linux2.6.i386.rpm
-rw-r–r– 1 root root 6270129 Sep 7 23:50 MySQL-devel-5.5.15-1.linux2.6.i386.rpm
-rw-r–r– 1 root root 47130066 Sep 7 23:53 MySQL-embedded-5.5.15-1.linux2.6.i386.rpm
Instalace
[root@testsrv01 tsolar]# rpm -i MySQL-server-5.5.15-1.linux2.6.i386.rpm
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 testsrv01 password ‚new-password‘
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
[root@testsrv01 tsolar]# rpm -i MySQL-client-5.5.15-1.linux2.6.i386.rpm
[root@testsrv01 tsolar]# rpm -i MySQL-devel-5.5.15-1.linux2.6.i386.rpm
[root@testsrv01 tsolar]# find / -name „mysql“
/usr/bin/mysql
[root@testsrv01 tsolar]# cd /usr/bin/
[root@testsrv01 bin]# ls -ltr mysql*
-rwxr-xr-x 1 root root 3888 Jul 13 13:06 mysql_zap
-rwxr-xr-x 1 root root 17473 Jul 13 13:06 mysql_setpermission
-rwxr-xr-x 1 root root 8066 Jul 13 13:06 mysql_secure_installation
-rwxr-xr-x 1 root root 14556 Jul 13 13:06 mysql_install_db
-rwxr-xr-x 1 root root 32819 Jul 13 13:06 mysqlhotcopy
-rwxr-xr-x 1 root root 1261 Jul 13 13:06 mysql_fix_extensions
-rwxr-xr-x 1 root root 3315 Jul 13 13:06 mysql_find_rows
-rwxr-xr-x 1 root root 7402 Jul 13 13:06 mysqldumpslow
-rwxr-xr-x 1 root root 22023 Jul 13 13:06 mysqld_safe
-rwxr-xr-x 1 root root 23199 Jul 13 13:06 mysqld_multi
-rwxr-xr-x 1 root root 4245 Jul 13 13:06 mysql_convert_table_format
-rwxr-xr-x 1 root root 6853 Jul 13 13:06 mysql_config
-rwxr-xr-x 1 root root 11332 Jul 13 13:06 mysqlbug
-rw-r–r– 1 root root 1702 Jul 13 13:06 mysqlaccess.conf
-rwxr-xr-x 1 root root 111560 Jul 13 13:06 mysqlaccess
-rwxr-xr-x 1 root root 3685371 Jul 13 13:07 mysql_waitpid
-rwxr-xr-x 1 root root 3550605 Jul 13 13:07 mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root 6653624 Jul 13 13:12 mysql
-rwxr-xr-x 1 root root 3807302 Jul 13 13:12 mysql_upgrade
-rwxr-xr-x 1 root root 5976698 Jul 13 13:12 mysqladmin
-rwxr-xr-x 1 root root 5966564 Jul 13 13:12 mysqlcheck
-rwxr-xr-x 1 root root 6156041 Jul 13 13:12 mysqldump
-rwxr-xr-x 1 root root 5972569 Jul 13 13:12 mysqlimport
-rwxr-xr-x 1 root root 6453811 Jul 13 13:12 mysqlbinlog
-rwxr-xr-x 1 root root 6016534 Jul 13 13:12 mysqlslap
-rwxr-xr-x 1 root root 5961754 Jul 13 13:12 mysqlshow
-rwxr-xr-x 1 root root 6464538 Jul 13 13:12 mysqltest
Spuštění serveru
[root@testsrv01 bin]# /usr/bin/mysqld_safe &
[1] 7370
[root@testsrv01 bin]# 110908 01:26:56 mysqld_safe Logging to ‚/var/lib/mysql/testsrv01.err‘.
grep: grep“: No such file or directory
110908 01:26:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Nastavení hesla pro root MySQL serveru
[root@testsrv01 bin]# /usr/bin/mysqladmin -u root password ‚mysql‘
Přihlášení
[root@testsrv01 bin]# mysql -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, 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.
Vytvoření uživatele
mysql> create user ‚tsolar’@’localhost‘ identified by ‚mysql‘;Query OK, 0 rows affected (0.00 sec)(S databázi sampdb budu pracovat dále)mysql> grant all on sampdb.* to ‚tsolar’@’localhost‘;Query OK, 0 rows affected (0.00 sec)
ROZDÍL
V MySQL můžete přiřadit práva k databázi aniž by existovala. V Oracle musí nejdříve existovat databáze, do které se přihlásíte a teprve v ní pracujete.
mysql> quitByeYou have new mail in /var/spool/mail/root[root@testsrv01 bin]# mysql -p -u tsolarEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.5.15 MySQL Community Server (GPL)Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Základní SQL dotazy
Type ‚help;‘ or ‚\h‘ for help. Type ‚\c‘ to clear the current input statement.
mysql> select now();
+———————+
| now() |
+———————+
| 2011-09-08 01:53:15 |
+———————+
1 row in set (0.00 sec)
mysql> SELECT NOW(), USER(), VERSION()\G
*************************** 1. row ***************************
NOW(): 2011-09-08 02:24:23
USER(): tsolar@localhost
VERSION(): 5.5.15
1 row in set (0.00 sec)
mysql> SELECT NOW(),
-> USER(),
-> VERSION()
-> ;
+———————+——————+———–+
| NOW() | USER() | VERSION() |
+———————+——————+———–+
| 2011-09-08 02:25:40 | tsolar@localhost | 5.5.15 |
+———————+——————+———–+
1 row in set (0.00 sec)