Search by tag: mariadb

3 articles

MariaDB / MySQL: generate a row number

There is currently no built-in method to return row numbers. The solution is to use a variable which is incremented in each row, like this:

@currentRow := @currentRow + 1 AS rowNumber

We can use a JOIN statement to initialise the variable without SET:

JOIN (SELECT @currentRow := 0) row

Another notation, replacing JOIN with a comma:

, (SELECT @currentRow := 0) row

Here is an example of an entire query:

SELECT
    title,
    text,
    @currentRow := @currentRow + 1 AS rowNumber
FROM articles
JOIN (SELECT @currentRow := 0) row

Source

MariaDB / MySQL: export or backup data to a CSV file

It seems there are several ways to do this. Here is one, using the mysql prompt. I used mysql root account because my usual user has not enough permissions to write files (ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)), even after a chmod 777: UPDATE: We need to grant file access to the database user:

$ mysql -u root -p
Enter password:
[...]
MariaDB [(none)]> GRANT FILE ON *.* TO 'username'@'localhost';

GRANT ALL does not include file permission. Now we can execute a query and output it into a CSV file. Formatting options are available:

$ mysql -u username -p database_name
MariaDB [database_name]> SELECT field1, field2, field3 INTO OUTFILE 'database_name_export.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM table_name;

Not specifying a path put the file in /var/lib/mysql/database_name. When I use /tmp/database_name_export.csv, the file is written in /tmp/systemd-private-14da7...-mysqld.service-.../tmp/. This behaviour happens on an encrypted Arch Linux system.

Also note that mysql won't overwrite an existing file, for security reason: ERROR 1086 (HY000): File '/tmp/database_name_export.csv' already exists.

Source

Source update

MariaDB / MySQL: backup and restore a specific table

We just have to specify the name of the table we want to backup in the usual mysqldump command:

$ mysqldump -h hostname -u username -p database_name table_name > backup_table_name.sql

Then, to restore it:

mysql -u username -p database_name

Source