cloud engineer
The standard way to connect to MySQL is:
mysql -h hostname -u user -p |
mysql -h hostname -u user -p
Here’s how to connect to MySQL with SSL encryption.
mysql -h hostname -u user -p \ --ssl-ca=server-ca.pem \ --ssl-cert=client-cert.pem \ --ssl-key=client-key.pem |
mysql -h hostname -u user -p \ --ssl-ca=server-ca.pem \ --ssl-cert=client-cert.pem \ --ssl-key=client-key.pem
Generate the SSL keys from the MySQL server. Download it to the client.
Here’s how to restore a MySQL database from mysqldump.
mysql -u user -p mysql> drop database databasename; mysql> quit; Bye mysql -u user -p databasename < filename.sql |
mysql -u user -p mysql> drop database databasename; mysql> quit; Bye mysql -u user -p databasename < filename.sql
Drop database first, then import the SQL file.
Here’s my MySQL backup script to the S3 Bucket.
Just a couple of things about the script. It’s using …
They must be setup and configured to work properly.
#!/bin/bash cd /root/database TIMESTAMP=$(date +%Y-%m-%d) S3FILE="s3://bucketname/sqlbackup/backup-$TIMESTAMP.sql" /usr/bin/mysqldump dbname > dbname.sql /usr/local/bin/aws s3 cp dbname.sql $S3FILE sleep 3s rm dbname.sql |
#!/bin/bash cd /root/database TIMESTAMP=$(date +%Y-%m-%d) S3FILE="s3://bucketname/sqlbackup/backup-$TIMESTAMP.sql" /usr/bin/mysqldump dbname > dbname.sql /usr/local/bin/aws s3 cp dbname.sql $S3FILE sleep 3s rm dbname.sql
Finally, set the S3 bucket with a 7 day retention. Backups older than 7 days are automatically deleted.