MySQL - Mariadb 2021
< Configure the INN nntp server (2021/2022) | Configuring a small home server in 2021 with openSUSE Leap 15.3 | Installing Apache2 (2022) >
I always fear to install mysql/mariadb, 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" it's now "mariadb" or something similar. but with exactly the same commands (or nearly). Of course installing it with YaST is obvious...
Below the old page content:
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 you 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 done 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, except for "USE" and "QUIT" (where 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:
- /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. < Configure the INN nntp server (2021/2022) | Configuring a small home server in 2021 with openSUSE Leap 15.3 | Installing Apache2 (2022) >