23, 42, RDF and more
Databases
Database: Migration from SQLite to MySQL
0After working with some sqlite databases for one of my iOS projects lately, I have now decided to also add an online service for the project. But how can I migrate from sqlite to MySQL?
Here are the basic three steps, I figured out that are needed to extract and modify the DDL to be ready for an import to MySQL:
- 1. Export the SQLite database with sqlite and command parameter “.dump” – as an example : sqlite3 mySQLiteDataBase .dump .quit >> myDumpSQLite
- 2. Adapt the dump to get it compatible for MySQL – Replace “ (double-quotes) with ` (grave accent) – Remove “BEGIN TRANSACTION;” “COMMIT;”, and lines related to “sqlite_sequence” – Replace “auto increment” with “auto_increment”
- 3. The dump is ready to get imported in a MySQL server
Luckily the modifications can be done with a simple python script Axel Steiner wrote. You can find it on his website:
How to drop multiple tables in MySQL
0If you want to drop multiple tables in MySQL (current version 5.05 or before) you will find no command for that. But there is a simple way using some console commands, just hit your shell and simply paste this snippet:
mysqldump -u [USER] -p [PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u [USER] -p [PASSWORD] [DATABASE]
Do not forget to replace [USER], [PASSWORD] and [DATABASE] with your settings. As always with remove operations be careful and backup!!!
Scalable second-generation distributed database
0A good friend of mine just showed me two links to scalable second-generation distributed database, I want to share with you.
This approach is my favorite one – as it is the underlying database of Digg, Facebook, Twitter and many others. It is written in Java and released under the Apache License:
The other approach is written in C and released under the GNU General Public License Version:
MySQL Replication Setup
0On the Master we first flush the tables in the database setting a read lock:
USE phpMyWebcam; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
The last command will show something like this
| +——————+———-+————–+——————+ | File | Position | Binlog_do_db | Binlog_ignore_db | +——————+———-+————–+——————+ | mysql-bin.015586 | 364167 | phpMyWebcam | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) |
We write down this information, as we will need it later on the slave!
We leave the MySQL shell and execute:
mysqldump -u root -p<password> –opt phpMyWebcam > phpMyWebcam.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)
This will create an SQL dump of phpMyWebcam in the file phpMyWebcam.sql. Transfer this file to your slave server!
Finally we have to unlock the tables in phpMyWebcam:
mysql -u root -p Enter password:
mysql> UNLOCK TABLES;
quit;
Now the configuration on the master is finished. On to the slave…
If you have made an SQL dump of phpMyWebcam on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created phpMyWebcam on the slave:
mysql -u root -p<password> phpMyWebcam < /path/to/phpMyWebcam.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)
Finally, we must do this:
mysql -u root -p
Enter password:
SLAVE STOP;
In the next command (still on the MySQL shell) you have to replace the values appropriately:
CHANGE MASTER TO MASTER_HOST=‘localhost’, MASTER_PORT=13306, MASTER_USER=’replicant’, MASTER_PASSWORD=’<some_password>’, MASTER_LOG_FILE=’mysql-bin.015586′, MASTER_LOG_POS=364167;
- MASTER_HOST is the IP address or hostname of the master (in this example it is localhost).
- MASTER_PORT is the port to which the sql master listens (in this example it is 13306).
- MASTER_USER is the user we granted replication privileges on the master.
- MASTER_PASSWORD is the password of MASTER_USERon the master.
- MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS;on the master.
- MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
Now all that is left to do is start the slave. Still on the MySQL shell we run
START SLAVE;
quit;
That’s it! Now whenever phpMyWebcam is updated on the master, all changes will be replicated to phpMyWebcam on the slave. Test it!
To see the status of your slave you can every time run this command on the slave:
SHOW SLAVE STATUS;
Purge MySQL Database
0If you run out of disk space on your server, it might be a good time to check your mysql binary logs. If there is really to much space used than just issue some SQL commands and you can delete them.
To delete all binary logs older than 7 days:
mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
To purge all logs before a specific date:
mysql> PURGE MASTER LOGS BEFORE ’2008-01-01 00:00:00′;
To purge logs automatically (every Monday at 3am) you could use a Unix cron job:
0 3 * * mon mysql -uroot -e “PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);”
MySQL Problems
0* MYSQL WON’T START OR STOP?:
=============================
You may never ever delete the special mysql user “debian-sys-maint”. This user
together with the credentials in /etc/mysql/debian.cnf are used by the
init scripts to stop the server as they would require knowledge of the
mysql root users password else.
So in most of the times you can fix the situation by making sure that
the debian.cnf file contains the right password, e.g. by setting a new
one (remember to do a “flush privileges” then).
* WHAT TO DO AFTER UPGRADES:
============================
 - running mysql_fix_privileges_tables to be able to make use of
  possibly added new privilege columns. This script does not give
  any use more rights.
* WHAT TO DO AFTER INSTALLATION:
================================
The MySQL manual describes certain steps to do at this stage in a separate
chapter. They are not necessary as the Debian packages does them
automatically.
The only thing that is left over for the admin is
 - setting the passwords
 - creating new users and databases
 - read the rest of this text
* SSL/TLS SECURE NETWORKING:
============================
Please take a look at my SSL-MINI-HOWTO.txt which is in this directory, too.
* NETWORKING:
=============
For security reasons, the Debian package has enabled networking only on the
loopback device using “bind-address” in /etc/mysql/my.cnf. Check with “netstat
-tlnp” where it is listening. If your connection is aborted immediately see if
“mysqld: all” or similar is in /etc/hosts.allow and read hosts_access(5).
* WHERE IS THE DOCUMENTATION?:
==============================
As the documentation is under a different licence than the rest of MySQL it
had to be separated into the mysql-doc package which is in the non-free branch
of Debian. You can find the Changelogs/News there, too.
* PASSWORDS:
============
It is strongly recommended to set a password for the mysql root user
(which is NOT the same as the “normal” root user) with the command:
 /usr/bin/mysqladmin -u root password ‘enter-your-good-new-password-here’
If you already had a password set add ” -p ” before “-u” to the line above.
If you are tired to type the password in every time or want to automate
your scripts you can store it in the file $HOME/.my.cnf. It should be
chmod 0600 (-rw——- username username .my.cnf) to ensure that nobody
else can read it. Every other configuration parameter can be stored
there, too. You will find an example below and more information in the
MySQL manual in /usr/share/doc/mysql-doc or www.mysql.com.
ATTENTION: It is necessary, that a .my.cnf from root always contains a “user”
line whereever there is a “password” line, else, the Debian maintenance
scripts, that use /etc/mysql/debian.cnf, will use the username
“debian-sys-maint” but the password that is in root’s .my.cnf. Also note,
that every change you make in the /root/.my.cnf will affect the mysql cron
script, too.
       # an example of $HOME/.my.cnf
       [mysql]
       user           = your-mysql-username
       password       = enter-your-good-new-password-here
       [mysqladmin]
       user           = your-mysql-username
       password       = enter-your-good-new-password-here
* BIG_ROWS FOR EVEN MORE ROWS IN A TABLE:
=========================================
If you ever run out of rows in a table there is the possibility of building the
package with “-DBIG_ROWS” which, according to a MySQL employee on
packagers@lists.mysql.com should leat to a 64bit row index (I guess > 2^32 rows)
but also to an aprox. 5% performance loss.
