Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 355

mysqldump --user= admin_backup --password --lock-tables \
--databases birdwatchers > birdwatchers.sql
mysql --user= admin_maintenance --password \
--execute "DELETE FROM birdwatchers.humans
WHERE name_first = 'Lexi'
AND name_last = 'Hollar';
UPDATE birdwatchers.humans
SET email_address=''
WHERE name_first = 'Nina'
AND name_last = 'Smirnova'"
After executing this, log into MySQL to confirm there is no member with the name Lexi
Hollar and no email address for Nina Smirnova in the humans table. You should do this
even though you may be logically satisfied that these changes were made. It's good to go
through the motions to build more confidence in the restoration process.
Now let's import the birdwatchers database into a temporary table. Edit the bird-
watchers.sql dump file you just created and look for the SQL statements that reference the
database — there should be onlythe CREATE DATABASE statement and the USE state-
ment. Change the database name wherever it occurs to birdwatchers_backup , as-
suming that this name doesn't already exist on your server. When you've done that, save
the dump file and exit it. From the command line, execute the following to import it:
mysql --user= admin_maintenance --password < birdwatchers.sql
When you've finished importing the database, log into MySQL andrun SHOW
DATABASES to see that it has been created. Now you're ready to restore the data in the
humans table. Execute the following from within the mysql client:
REPLACE INTO birdwatchers . humans
SELECT * FROM birdwatchers_backup . humans
WHERE name_first = 'Lexi' AND name_last = 'Hollar' ;
UPDATE birdwatchers . humans
SET email_address = '[e-mail]'
WHERE name_first = 'Nina' AND name_last = 'Smirnova' ;
That will restore the row for the member that was deleted, restore the email address for
the other member, and have no effect on the other rows or other tables in the database.
You'll notice I used the REPLACE statementinstead of the INSERT statement. If MySQL
finds a row that matches the WHERE clause and that has the same human_id , it will re-
place the row with the matching row from the backup table. Otherwise, it will insert a new