Doc /

Mysql (Mariadb) - 2017

<-Back

I always fear to install mysql, because I mind the way of backing up and restoring the database.

That said it's difficult to always avoid it, I was obliged to have one for my photo gallery. It's not difficult, but as often the documentation is obscure.

Install and initial configuration

Install

Install it with YaST. The package is no more "mysql" since Oracle buy it, it's now "mysql-community-server" or something similar. But there is a new variant named "mariadb", but with exactly the same commands. Of course installing it with YaST is obvious... Better install mariadb.

You can also install with "zypper in mariadb*".

Initial setup

Open a root terminal and run "mysql_secure_installation".

This is a command line utility that remove all the demo bases, open to the world and so very unsecure and set root passwd.

Before launching this utility, of course, think of a strong root passwd (root in the sense of mysql root, nothing to do with the root of the server). Think also at a basic user, and the according passwd.

Creating root passwd

Of course you may have missed the initial script :-(. If so you have to create immediately a passwd for the mysql "root".

 $ mysqladmin -u root password NEWPASSWORD

NEWPASSORD is the password, the rest is the command. This can be only do once, after that, to change it eventually

 $ mysqladmin -u root -p'oldpassword' password newpass

Use mysql cli

Help

"?" quotation mark gives list of commands,

Quotation mark or command "help" followed by the name of a command gives help about this command. Final ";" is not mandatory, but do not hurt.

Upper case in commands is optional, simply better in examples.

The "mysql" base is where mariadb stores it's administrative data, so have to be there.

Connect

 mysql -p -u

-p makes mysql ask for passwd. -u is not mandatory is you are root.

"mysql" gives you a command prompt. Exit with "quit" (may be also Ctrl C).

Create an user

First create user jdd with his passwd

 >create user 'jdd' identified by 'motdepasse';

Create a database for this user

 >CREATE DATABASE jddbase;

(don't forget the space between CREATE and DATABASE) and the final ";", only letters are allowed.

Create the jdd base, on wich jdd will have the admin rights given below (the user name don't have to be the same as the database):

 >GRANT ALL ON jddbase.* to jdd;

The ";" is mandatory, safe for "USE" and "QUIT" (there it's optional).

 >USE jddbase;

After that, jdd can create tables, that is let your application create them.

An ordinary user only need one data base, in it he can create an unlimited number of tables, adding a mark to the name. However, if ever one may have to remove tables for a application, it's better to have one database for each app, as so it's enough to remove the base.

List

Bases

 >show databases;

Tables

 >use database_name;
 >show tables;

Only some tables:

 > show tables like 'wp\_%' ;

Jokers in mysql have to be put after "like" with chain between simple quotes ('), % is used where bash uses "*" and "_" where "?". The jokers can be escaped as in bash, so the "\_" to search after wp_ (the default Wordpress tables).

Contents

 >select * from table_name;

Users

 >use mysql;
 >select * from user;

But this gives too much infos for a screen, so

 select host, user, password from mysql.user;

for example gives only the three fields, the hash for the pass (not the pass itself).

To have a simple display. In the host column, % mean users home:

select host, user from mysql.user;

+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | abc       |
| %         | defgh  |
| %         | ijk       |
| localhost | root      |
| localhost | roundcube |
+-----------+-----------+

Drop tables or bases

The "drop" command do not accept wild cards for safety, but the TAB completion works, so dropping many tables is tedious but doable without script.

 drop tables wp_comm <TAB> ;

PageUp history also works, so (in mysql) typing "s" then PageUp gives you the last show command used.

 DROP DATABASE bufg;

remove database bufg.

Backup

On command line, as easy, use "mysqldump". Options are "--opt" for the most common options, "-u <name>" for the user, "-p" to ask the pass, then the db name, the ">" pipe and the final file name.

 mysqldump --opt -u root -p BASE TABLE > xxx.sql

restore simply with

 mysql -u root -p jdd-base < jdd-base.sql

notice that I often have problem with restores...

to use mysqldump with only a sub part of tables. This same script can be used to build any batch command for mysql. Works also typing each line on command line.

cat dumpwp.sh

#!/bin/sh

echo mysqldump -u USER -p BASE --tables > ta.txt
#USER: owner of the base. May be root. -p ask for passwd
#this first line build a command as first line

mysql -u USER -p BASE -Bse "show tables like 'wp\_%'  ;" >> ta.txt 

This line asks for passwd, then add to the file 
#the list of tables in the "like". 
#\_ is _ escaped, mean wp_ % mean "all"

cat ta.txt | tr "\n" " " > 2ta.sh

#this line remove all CR in the file to give a single line for next command

sh 2ta.sh > wp.sql
#execute the line
May not work if line is too long?

Change root passwd

Not that easy!

 rcmysql stop
 mysqld_safe --skip-grant-tables --user=root &

(the key is the "safe" part - run as root, not as mysql user)

 mysql -u root
 UPDATE mysql.user SET password=PASSWORD("motdepasse") WHERE user="root";

(password en entier, ; à la fin)

 quit
  • il faut tuer gentilment (kill, mais pas -9) tous les process mysqld_safe (je n'ai pas trouvé d'autre moyen de l'arréter)
 rcmysql restart

ou

 systemctl restart mysql.service

et tester avec

 mysql -u root -p 

(il vous demande le mot de passe)

Repair a table

Log in mysql (as user jdd is ok) connect to the base (connect jdd)

 check table <table name>;

should give clues of the problem

 repair table <table name>;

should fix the problem if possible. if not, reload a backup.

 mysqlcheck -u root -p  --auto-repair  --optimize --all-databases

Insert data

For example a previous backup

 mysql -u root -p database_name < file.sql

ask for root passwd and use pipe to send the backup file (that is only a text file with sql commands).

Notice that this needs the database to be pre-existent.

Changer le mot de passe d'un user

Seul root peut le faire

 SET PASSWORD FOR 'MonNomDeUSER' = PASSWORD('Nouv-MotDEPasse');

transfert

 rsync -av -e ssh dodin.org:/var/lib/mysql/ .

dans le nouveau /var/lib/mysql (il faut lancer mysql avant pour créer le répertoire).

Warning: a database can be hudge, probably because the indexes, becaus the dump is relatively small. A sql file or 80 Mo can be related to a database of 1.5 Go. That mean than letting the database stay in /var/lib is ridiculous if /var is on /.

You can erase /var/lib/mysql, it will be re-created at mysql launch (better verify every mysql instance is stopped). Then create a mysql folder anywhere there is room (/data or /home) and link to it;

 ln -s /home/mysql /var/lib/mysql

the database follows the links.

Digikam

Digikam est lié à mysql, mais seulement en local (sur mon poste de bureau).

Pour relier Digikam à la base mysql, j'ai du donner l'user et le mot de passe root. Dans le menu migration, le champ option est à laisser vide.

Pour voir les tables, utiliser mysqlshow (hors de mysql):

 mysqlshow digik -u root -p

digik est la table.

RÉCUPÉRATION DU MOT DE PASSE ROOT

voir tout en bas de page

Arrêter MySQL:

  1. /etc/init.d/mysql stop ou systemctl stop mysql.service

Démarrer le serveur MySQL sans mot de passe:

  1. mysqld_safe --skip-grant-tables &

Se connecter à MySQL en tant que root:

#mysql -u root
>use mysql;
>update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
>flush privileges;
>quit

Redémarrer le serveur MYSQL.

 #/etc/init.d/mysql restart

ou

 #systemctl  start mysql.service

Tester votre nouveau mot de passe root:

 #mysql -u root -p

SAUVEGARDES MYSQL

Sauvegarder toutes vos bases de données: -u = utilisateur ; -p = mot de passe (il n'y a pas d'espace entre le mot clef "-p" et le mot de passe);

  1. mysqldump -u root -prootpassword --all-databases > mysql_databases_backup.sql

Sauvegardez une base de données spécifique:

  1. mysqldump -u root -prootpasssword --databases database_name > mysql_database_backup.sql

Voir les détails à propos de la commande mysqldump.

Restaurer votre sauvegarde MySQL:

  1. mysql -u root -proot < sql_backup.sql

Il est important de souligner que quand vous sauvegardez une base de données, cela ne sauvegardera AUCUN utilisateur MySQL. Comme les utilisateurs MySQL sont stockés dans la table "user" de la base de données "mysql", c'est une bonne idée de sauvegarder la base de données "mysql" également.

Ci-dessous, un exemple où les bases de données MySQL sont sauvegardées périodiquement.

Premièrement, nous créons un utilisateur MySQL avec des permissions restreintes pour sauvegarder les bases de données:

  1. mysql -u root -p

>GRANT SELECT , SHOW DATABASES , LOCK TABLES ON * . * TO backupuser@localhost IDENTIFIED BY 'password' ; Deuxièmement, nous créons un script shell en copiant la ligne ci-dessous dans le fichier appelé mysqlbackup.sh dans le dossier /home/sam où sam est un utilisateur de votre choix:

  1. vi /home/sam/mysqlbackup.sh

date=`date -I`; mysqldump --all-databases -u backupuser --password="password" > databasebackup-$date.sql La date sera ajoutée à la fin du nom du fichier. Par exemple, un fichier sauvé le 4 janvier 2007 sera appelé databasebackup-2007-01-04.sql.

  1. chown sam /home/sam/mysqlbackup.sh
  2. chmod 700 /home/sam/mysqlbackup.sh

Mettez les droits appropriés pour le fichier mysqlbackup. La propriété du fichier est donnée à sam avec tous les droits. (lecture, écriture, exécution)

Troisièmement, nous ajoutons la ligne suivante dans la crontab de notre utilisateur Linux:

  1. crontab -e -u sam

30 00 * * * /home/sam/mysqlbackup.sh Le script shell mysqlbackup sera lancé chaque jour à 00:30 et ainsi sauvegardera les bases de données MySQL.