Mysql (Mariadb) - 2017
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:
- /etc/init.d/mysql stop ou systemctl stop mysql.service
Démarrer le serveur MySQL sans mot de passe:
- 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);
- mysqldump -u root -prootpassword --all-databases > mysql_databases_backup.sql
Sauvegardez une base de données spécifique:
- mysqldump -u root -prootpasssword --databases database_name > mysql_database_backup.sql
Voir les détails à propos de la commande mysqldump.
Restaurer votre sauvegarde MySQL:
- 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:
- 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:
- 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.
- chown sam /home/sam/mysqlbackup.sh
- 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:
- 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.