MySQL/Databases manipulation
Creation
edit CREATE DATABASE database;
Require? Privilege.
mysqladmin create
is a command-line wrapper for this function.
NB: in MySQL, CREATE SCHEMA
is a perfect synonym of CREATE DATABASE
, contrarily to some other DBMS like Oracle or SQL Server.
Deletion
edit DROP DATABASE database;
Require ? privilege.
mysqladmin drop
is a command-line wrapper for this function. The -f
option can be used to suppress the interactive confirmation (useful for unattended scripts).
Rename
editIn some 5.1.x versions there was a RENAME DATABASE db1 TO db2;
command, but it has been removed because renaming databases via SQL caused some data loss problems[1].
However, in the command-line, you can create/export/import/delete:
mysqladmin create name2
mysqldump --opt name1 | mysql name2
mysqladmin drop -f name1
Another option, if you have root access, is to rename the database directory:
cd /var/lib/mysql/
/etc/init.d/mysql stop
mv name1/ name2/
/etc/init.d/mysql start
You also need to drop privileges on name1 and recreate them on name2:
UPDATE mysql.db SET `Db`='name2' WHERE `Db`='name1';
FLUSH PRIVILEGES;
Copy
editThere is no direct copy command in MySQL. However, this can easily be done using some tools.
With mysqldump
editThe mysqldump command-line can be used to generate a complete flat-file copy of the database. You can then reinject this copy in another database.
This requires a direct access to the database; if you do not have it, you may need to use phpMyAdmin instead.
# First, clean-up the target database:
mysqladmin drop -f base2
mysqladmin create base2
# Copy base1 to base2:
mysqldump --opt base1 | mysql base2
Backup
editTo set an automatic backup every day at midnight[2], in Linux:
$ crontab -e
0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS base1 | gzip -c > `date “+\%Y-\%m-\%d”`.gz
With phpMyAdmin
edit
Restoration
edit- With Linux:
mysql -h localhost -u root MaBase < MaBase.sql
- With Windows, the program may not be into the environment variables:
"C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe" -h localhost -u root MyDB < MyDB.sql
Contrarily to the PhpMyAdmin importations, there is no limit. For example, we can load a 2 GB database in five minutes.
Migration from other databases
edit This section is a stub. You can help Wikibooks by expanding it. |
Tools: MySQL Migration Toolkit
Tools for data modeling
edit
DB Designer 4 and MySQL Workbench
editDBDesigner begins to be old. It is released under the GNU GPL, but it cannot be fully considered as free software since it requires the non-free Kylix compiler to build.
But MySQL AB acquired fabFORCE [citation needed][3], who distributed DB Designer, and MySQL Workbench is the next version. For now the project is still Alpha and not ready for use yet.
Meanwhile, if you use the latest release of DBDesigner, you'll find that it cannot connect to MySQL, with the "unable to load libmysqlclient.so" error. To workaround this,
- Install the MySQL "Shared compatibility libraries" (from http://dev.mysql.com/downloads/mysql/5.0.html#downloads for version 5.0, generic RPMS aka MySQL-shared-compat.i386 will do).
- Replace DBDesigner's version of libmysqlclient.so with the newly installed one:
sudo ln -sf /usr/lib/libmysqlclient.so.10 /usr/lib/DBDesigner4/libmysqlclient.so
- Find and install
kylixlibs3-unwind-3.0-rh.4.i386.rpm
- Find an old xorg (e.g.
xorg-x11-libs-6.8.2-37.FC4.49.2.1.i386.rpm
from FC4) and extract it:
rpm2cpio x.rpm | cpio -i
- Get libXft.so.1.1 in that package and install it:
sudo cp libXft.so.1.1 /usr/lib ldconfig
You now can connect to your MySQL5 server from DBDesigner4. Consider this a temporary work-around waiting for community (free) and commercial (not free) versions MySQL Workbench.
OpenOffice Base and ODBC
editTypical configuration :
- MySQL database on a host machine (which name is
mysqlhost
below) - OOo 2 on a client machine (Debian GNU/Linux for instance)
- Connection via ODBC.
It's a client configuration : we need mysql-client
:
aptitude install mysql-client
Under Fedora/CentOS:
yum install mysql
Before installing ODBC, we can test the remote connexion locally:
$ mysql -h mysqlhost -u user1 mysqldatabase -p Enter password: PassUser1
You must have create the database mysqldatabase
and the user user1
on mysqlhost
.
It seems there is no problem (hope there is not ;-)):
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 to server version: 5.0.24a-Debian_5~bpo.1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Then, it's possible to test, through different queries :
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysqldatabase | +--------------------+ 2 rows in set (0.00 sec) .... mysql> quit; Bye
Fine ! Let's go with OOo and ODBC, on the client machine:
aptitude install libmyodbc unixodbc
For Fedora/CentOS:
yum install mysql-connector-odbc unixODBC
/etc/odbc.ini
(empty file) and /etc/odbcinst.ini
are created.
odbcinst.ini
declares the available ODBC driver. Here's the MySQL statement (paths to the .so files may vary depending on the distribution); for Debian:
[MySQL] Description = MySQL driver Driver = /usr/lib/odbc/libmyodbc.so Setup = /usr/lib/odbc/libodbcmyS.so CPTimeout = CPReuse = FileUsage = 1
for CentOS:
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc3.so Setup = /usr/lib/libodbcmyS.so FileUsage = 1
Now we can use odbcinst
:
# odbcinst -j unixODBC 2.2.4 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /root/.odbc.ini
For further options : man odbcinst
First of all, we have to create at least one DSN (Data Source Name or Data Set Name), because every ODBC connection is initialized through an existing DSN. It's true in every cases, so it is required for an ODBC connection from OOo.
To create a DSN, one have different possibilities :
- Modify /etc/odbc.ini (concerns all users)
- Modify ~/.odbc.ini (concerns a specific user)
- Use graphical applications such as ODBCConfig (Debian:
unixodbc-bin
, Fedora:unixODBC-kde
). Finally, these graphical applications modify /etc/odbc.ini or ~/.odbc.ini
For instance, a /etc/odbc.ini
file (the name of the DSN is between brackets []):
[MySQL-test] Description = MySQL ODBC Database TraceFile = stderr Driver = MySQL SERVER = mysqlhost USER = user1 PASSWORD = DATABASE = mysqldatabase
In that case, the DSN is called MySQL-test
Then we can test, using isql command:
$ isql -v MySQL-test user1 PassUser1 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-------------------+ | Database | +-------------------+ | information_schema| | mysqldatabase | +-------------------+ 2 rows affected 2 rows returned SQL> quit;
And now, from OOo:
-> File -> New -> Database -> Connecting to an existing database -> MySQL -> Next -> Connect using ODBC -> Next -> Choosing a Data Source -> MySQL-test -> Next -> Username : user1 (tick password required) -> Yes, register the database for me -> Finish
At that step, one is connected to the mysqldatabase database, under the user user1. Just before accessing the database, for example to create tables, one will give user1 password. Then, through OOo, it is now quite easy to access and manipulate the database. We can just notice that Java is required in the following cases :
- Wizard to create a form (at the opposite, to create a form directly don't need any JRE).
- Wizard to create reports.
- Wizard to create queries (at the opposite, to create a query directly or through a view don't need any JRE).
- Wizard to create tables (at the opposite, to create a table directly or to create a view don't need any JRE).
GNU/Linux distros usually ships OpenOffice with IcedTea (openjdk-6-jre
/java-1.6.0-openjdk
) or GCJ (java-gcj-compat
/java-1.4.2-gcj-compat
) so that these Java-based features work.
References
edit- ↑ https://dev.mysql.com/doc/refman/5.1/en/rename-database.html
- ↑ http://stackoverflow.com/questions/6645818/how-to-automate-database-backup-using-phpmyadmin
- ↑ In the forums: [1] but we'd need something more official