Execute native SQL with Doctrine

By emiviada in Databases, Doctrine 1 Comment

We can execute raw SQL queries with Doctrine ORM.

If you already used this awesome library you already know all the features Doctrine supplies us at the time to interact with our database. The DQL (Doctrine Query Language) language is a very powerful and one of the most main feature of Doctrine, but, sometimes we have to face really advanced queries and with DQL it could be a little tricky to do it (in version 2 it will be improved).

If you need to do advanced and complicated queries (or not), Doctrine gives you the ability to execute native/raw SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php

// we build the query
$query = "select id from tabla";

// gets the connection singleton
$con = Doctrine_Manager::getInstance()->connection();

// executes the query
$st = $con->execute($query);

// retrieves the results
$rs = $st->fetchAll();

// Or if you want the associative results
$rs = $st->fetchAssoc($query);

That’s it.

MySQL: Backup and Restoring

By emiviada in Databases, MySQL 5 Comments

In this post we will see how to make a MySQL backup and how to restore it.

If you are a web developer and even more if you programm with PHP surely you know that the most used database engine is MySQL. Of course, as a good developer you should know that is a golden rule to make and keep backups of your databases. Here we’ll see how to do it easily.

Back up from command line (using mysqldump)

We can backup our database using the mysqldump command, which connects with the database and generates a SQL dump file. This file has all the needed SQL queries to restore the database:

1
user@unix:~$ mysqldump --opt -u[uname] -p[pass] [dbname] > [backupfile.sql]

Where [uname] is the MySQL username, [pass] the user’s password, [dbname] is the database name, [backupfile.sql] is the output file name, and [–opt] are mysqldump options.

For instance, if we want to backup a database named ‘myBlog’ with a ‘root’ as username and the ‘mypass’ passowrd, and store the backup in a file called ‘myBlog_backup.sql’, we should run:

1
user@unix:~$ mysqldump -uroot -pmypass myBlog > myBlog_backup.sql

With mysqldump we can specify which tables we want to backup, for instance if we want to backup the ‘users’ and ‘posts’ tables, we should run:

1
user@unix:~$ mysqldump -uroot -pmypass myBlog users posts > myBlog2_backup.sql

Furthermore if we need to backup more than one database we can use the –databases option:

1
user@unix:~$ mysqldump -uroot -pmypass --databases myBlog car_shop clients > manydb_backup.sql

And also if we want to backup all the databases in MySQL we can use the –all-databases option:

1
user@unix:~$ mysqldump -uroot -pmypass --all-databases > alldb_backup.sql

Other utils options for mysqldump are:
–add-drop-table: Adds a DROP TABLE sentence before of each CREATE TABLE sentence in the dump.
–no-data: Backs up only the database structure with no data.
–add-locks: Adds the LOCK TABLES and UNLOCK TABLES sentences.

Back up whith compression

If our database is huge enough, we could want to compress the output with the following command:

1
user@unix:~$ mysqldump -u[uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

And to uncompress:

1
user@unix:~$ gunzip [backupfile.sql.gz]

Restoring the databases

Above we backed up a database called myBlog in a myBlog_backup.sql file. To restore it we should run the below commands:

1
user@unix:~$ mysql -u[uname] -p[pass] [db_to_restore] < [myBlog_backup.sql]

If we have compressed the dump:

1
user@unix:~$ gunzip < [backupfile.sql.gz] | mysql -u[uname] -p[pass] [dbname]

If we need to restore an existing database, we should run the mysqlimport command:

1
user@unix:~$ mysqlimport -u[uname] -p[pass] [dbname] [backupfile.sql]

That is. I hope it’s helful.