Recent Changes - Search:
Doc /

MySQL

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

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.

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

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 passwd

Of course toy may have missed the initial script :-(. So you also 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. No final ";" is mandatory, but do not hurt.

Connect

 mysql -p (-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).

mysql commands are always written uppercase in the doc but can be typed lowercase also.

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;

Then a database for this user: (don't forget the space between CREATE and DATABASE) and the final ";".

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 jdd.* to jdd;

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

 >USE jdd

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.

List bases

In the mysql shell: 

 >show databases;

The ";" at the end is mandatory for most mysql commands. If you don't type it, mysql displays a prompt to ask :-).

List tables (in a base)

 >use database_name;
 >show tables;

Only some tablest: 

 > 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 ascaped as in bash, so the "\_" to search after wp_ (the default Wordpress tables).

list content

 >select * from table_name;

list users

 mysql -u root -p
 >use mysql;
 >select * from user;

Backup

You can then install phpMyBackupPro "Installer phpMyBackupPro".

Gives it the (mysql) root passwd. Needs php-zip module.

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 jdd-base [table_name] > jdd-base.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 (it will ask you twice the pass): 

cat dumpwp.sh
#!/bin/sh
echo mysqldump -u jdd -p jdd --tables >ta.txt
mysql -u jdd -p jdd -Bse "show tables like 'wp\_%'  ;" >> ta.txt 
cat ta.txt | tr "\n" " " > 2ta.sh
sh 2ta.sh > wp.sql

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.