# MySQL/Databases manipulation

## Creation

``` sql
 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

``` sql
 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

In 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:

``` bash
 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:

``` bash
 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:

``` sql
 UPDATE mysql.db SET `Db`='name2' WHERE `Db`='name1';
 FLUSH PRIVILEGES;
```

## Copy

There is no direct copy command in MySQL. However, this can easily be
done using some tools.

### With mysqldump

The 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.

``` bash
# First, clean-up the target database:
 mysqladmin drop -f base2
 mysqladmin create base2
# Copy base1 to base2:
 mysqldump --opt base1 | mysql base2
```

#### Backup

To set an automatic backup every day at midnight[^2], in Linux:

``` bash
 $ crontab -e
0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS base1 | gzip -c > `date “+\%Y-\%m-\%d”`.gz
```

### With phpMyAdmin

left\|thumb\|upright=3


## Restoration

-   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

{{\...}}

Tools: MySQL Migration
Toolkit

## Tools for data modeling

-   MySQL Query Browser apparently includes a *MySQL Table Editor*
    module.
-   Kexi (wikipedia:
    Kexi)

### DB Designer 4 and MySQL Workbench

DBDesigner 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 [^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

Typical 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

```{=html}
<references />
```
fr:MySQL/Manipulation de
base

[^1]: <https://dev.mysql.com/doc/refman/5.1/en/rename-database.html>

[^2]: <http://stackoverflow.com/questions/6645818/how-to-automate-database-backup-using-phpmyadmin>

[^3]: In the forums:
    1 but we\'d
    need something more official
