1 / 25

MySQL Gubbins

MySQL Gubbins. Richard Sinclair HepSysMan – 11/05/2012. Agenda. Installation Users Server Parameters Security Backup and Recovery (the important bit) Coffee (the more important bit). Installation. $ yum install mysql-server mysql Edit my.cnf to specify data directory: [ mysqld ]

galena-hodges
Download Presentation

MySQL Gubbins

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQLGubbins Richard Sinclair HepSysMan – 11/05/2012

  2. Agenda • Installation • Users • Server Parameters • Security • Backup and Recovery (the important bit) • Coffee (the more important bit)

  3. Installation • $ yum install mysql-server mysql • Edit my.cnf to specify data directory: [mysqld] datadir=/var/data/mysql • $ /usr/bin/mysql_install_db • $ /usr/bin/mysql_secure_installation

  4. Installation 2 • Red Hat ships MySQL 5.1 • 5.5 Introduced: • Better multi-core performance • Better Partitioning (Truncate Partition) • Innodb as default storage engine • Performance schema • Install as RPM from MySQL website • Same Post Install Steps

  5. Storage Engines • MyISAM – default in 5.1: • Very fast read/writes • No row level locking (only table locks) • Separate datafiles for each table • Innodb – default in 5.5: • Row level locking • Foreign key support • Storage for all Innodb tables in single file (by default)

  6. Upgrading from 5.1 to 5.5 • Shutdown MySQL • Install new version from RPM • Run the mysql_upgrade script • If you get the “Table 'mysql.proxies_priv' doesn't exist” error then run: • $ mysqld --skip-grant-tables & • $ mysql_upgrade

  7. Starting/Stopping MySQL • Recommended to use mysqld_safe rather than mysqld start: • Will restart the MySQL server after a crash • Will read the [mysqld_safe] section of the my.cnf file as well as the [mysqld] section

  8. User Administration(The Bad Way) • CREATE USER 'monty'@‘%' IDENTIFIED BY 'some_pass'; • GRANT ALL PRIVILEGES ON *.* TO 'monty'@' %'WITH GRANT OPTION;

  9. User Administration(The Good Way) • CREATE USER 'monty'@' montysmachine.ac.uk' IDENTIFIED BY 'some_pass'; • GRANT ALL PRIVILEGES ON montydb.* TO 'monty'@‘montysmachine.ac.uk';

  10. Server Parameters • MySQL is pretty good straight ‘out of the box’ • There are a few parameters you can change to improve performance, improve logging and ensure disaster recovery is possible. • All set in /etc/my.cnf file

  11. Basic Settings datadir=/var/lib/mysql/data socket=/var/lib/mysql/mysql.sock user=mysql

  12. Log Files • log-error=/var/log/mysqld.log • slow_query_log_file=/var/log/slow.log • long_query_time = 60 • log-output=TABLE,FILE • mysql> SHOW CREATE TABLE mysql.slow_log;

  13. Performance Tweaks • MySQL is pretty quick but it can be made A LOT faster very easily. • These are the 2 parameters that have made the most difference for us….

  14. key_buffer_size • Allocates memory to cache indexes • Defaults to 8MB • Too low meaning that indexes are mostly read from disk needlessly • Increasing to 256MB on a 4GB system improved throughput by 300% (on MyISAM tables)

  15. innodb_buffer_pool_size • Only relevant when using InnoDB • Defines the maximum memory InnoDB can use to buffer data • Defaults to 128MB • Raising to 512MB on a 4GB system greatly increased throughput

  16. Disaster (Avoidance) • Enable Binary Logging (in my.cnf): • log-bin = /mysql-backup/binlog/bin.log • expire_logs_days = 10 • max_binlog_size = 256M • Ensure Binary Logs are on separate disk to Data Directory • Can be used for Point-in-Time recovery • More later….

  17. Security • Don’t use port 3306 • Only allow super user access from localhost (fixed by mysql_secure_installation script) • Don’t run MySQL as root (set user=mysql in my.cnf file) • Change name of root user: • mysql> UPDATE mysql.user SET user = ‘superman' WHERE user = 'root';

  18. SQL Injection Attacks • If your database is public facing via a web server: • Change root user name • Test, test and test web pages (using common injection strings – plenty on Google) • Do not grant FILE privilege to users

  19. Backups • Daily backups + binary logs should be all you need to return to any point in time. • Which tool to use for backups…….

  20. Backup Utilities • Mysqldump • Mysqlhotcopy • Mysql-zrm • Xtrabackup • About a hundred more….

  21. What We Do • Xtrabackup with a homebrewed wrapper which deals with alerts, cataloguing of backups, compression and more. • Very fast for innodb tables, table locks on MyISAM (no way to avoid this)

  22. Key Advantages • Catalogue (separate MySQL server) records Binary Log position so you know at what point in time the backup was taken. • Cleans out old backups based on user defined retention policy. • Easy to restore – drag and drop.

  23. Point In Time Recovery • Restore the databases from most recent backup • Get the binlog position from catalogue or text file in backup directory • Use mysqlbinlog tool to parse binlogs from position of backups onwards into .sql script • Run the .sql script from MySQL • Get a cuppa (beverage size is dependent on amount of changes in binlogs)

  24. Links • richard.sinclair@stfc.ac.uk • databaseservices@stfc.ac.uk • http://www.percona.com/software/percona-xtrabackup

  25. Questions? (and hopefully answers)

More Related