Y
There are several questions and I don't know how to answer them in order.First, when you write mysql -e "show databases" -u root what you get is a well presented list of your databases, that is, you get something similar as a result.+--------------------+
| Database |
+--------------------+
| base1 |
| base2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| etcetera |
+--------------------+
When you lock up mysql -e "show databases" -u root between backticks (`) what is done is an operation called command substitution, that is, what is executed within those characters will replace the name "something".When you write for DataB in itera about the values of that "something", which in this case would be the result of what was obtained with the consultation with the client mysql.I suggest instead of using the backticks better use the syntax $(comando)for examplefor DataB in $(mysql -e "show databases" -u root)
...
In the loop for, variable DataB will store, in each iteration of each value, the value of each of the results, then, following the output of that command that I put previously, the first result that would come out would be that of Database and I doubt you have a database called DatabaseThen you could escape that value.If you look at errors carefully (seeing errors is your best ally) one saysUnknown database 'Database'Which tells us he's going through the chain. Database as parameter to mysqldump and this indicates that there is, from there, a failure in our obtaining the bases.Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLESThere we see that mysqldump don't "gusta" something about the board information_schema.Something you could do would be copying and pasting this.for dbase in $( mysql -e "show databases" -u root )
do
[[ "$dbase" != "Database" && "$dbase" != *"_schema" ]] && mysqldump -u <usuario> -p<contraseña> "$dbase" > "$dbase.sql"
done
That's if you want to avoid exporting tables as information_schema or performance_schemaOfor dbase in $( mysql -e "show databases" -u root )
do
[[ "$dbase" != "Database" ]] && mysqldump --single-transaction -u <usuario> -p<contraseña> "$dbase" > "$dbase.sql"
done
If you want the tables that have the metadata. This way you might get an error on the table. information_schema that can have you careless.Another option you could use is the one written in the manual mysqldump man 1 mysqldumpmysqldump -u <usuario> -p<contraseña> -h <host> --all-databases > all_databases.sql
To export all databases in the file all_databases.sqlTo clarify, note as after the parameter -p I then put, and without spaces, the indication of writes there the password.Note on security.It is necessary to clarify that doing these operations, with the password displayed in the terminal, is dangerous as someone can see it, either by browsing your history or scrolling until I see her.An excole, who is also a fellow like me, did that, and when he touched me to clean his computer, I saw in his record the command mysql -h 127.0.0.1 -u root -placontraseña In a production database... it's one of those things that you laugh first and then you worry. Imagine that an evil sysadmin sees it, now imagine that that password is also that of the server or computer programmer by the typical recycled passwords...One way, though tedious, to mitigate that security issue is to remove the password next to the parameter -p and so, mysqldump, you will ask for the password every database you consult. This stops being tedious when you use the mysqldump -u root -p --all-databases > all_databases.sql since there he will only ask you once for all the databases and not for each.Another way to address the issue is that, after writing sensitive information, use the command history -c in bash.Another way to fix it is by editing the file ~./my.cnf and adding the entrance [mysqldump] with the respective configurations, for example.[mysqldump]
user = <usuario>
password = <contraseña>
host = <host>
port = <puerto>
I mean, in the end you'd have to have something like that, it's just[mysqldump]
user = root
password = asdlkfmasdkmacsdcasContraseña
host = 127.0.0.1
port = 3306
And ready, any of the answers I wrote earlier can run without parametersLike, -h <host>, -u <usuario>, -p , -p<contraseña>, -P <puerto>as these will be taken from that configuration file. Of course, this file can be read but that's why you change the permissions to 600 with chmod 600 ~/.my.cnf to give only read and write permissions to the user who owns that file.This last way is clear that anyone who has superuser permits can see it, so it depends on the security of the system you handle. If you don't like it, you can use putting the password on hand when mysqldump or mysql They ask.
