
Database Administration on Linux
This format is not available in this course
About course
The average person has no idea how much IT has to do with databases. But the system administrator understands how many processes generally depend on databases, and knows how to manage them.
We will teach you how to administer popular databases.
What do you need to take the course?
You need to understand the following topics: text editors, user and group management, file and directory permissions management, Linux process management.
Iptables, troubleshooting for network connections, package managers, getting information about the versions of installed programs and available packages for installation in the repositories, a general understanding of the work of web servers.
Course program
Introduction
LectureGeneral information, introduction.
LectureBasics of the SQL language. Requests.
LectureMySQL console commands. PHPMyAdmin.
PracticeLinux OS installation, MySQL installation, MySQL queries. Creation of databases and tables.
Databases, tables, queries
LectureConfiguration files of MySQL, changing the MySQL configuration.
LectureTypes of tables. System variables of MySQL. Types of cells with data, MySQL JOIN.
PracticeFilling of tables. Creation of users, assignment of privileges. MySQL JOIN.
Backup and recovery
LectureMethods for backing up databases and restoring them.
LectureFeatures of backing up MyISAM tables.
LectureFeatures of InnoDB table backup.
PracticePractice for restoring databases with the MyISAM and InnoDB table types.
Getting information about MySQL. Performance statistics of MySQL
LectureGetting information about MySQL performance.
LectureHeavy and slow queries.
LectureData about current requests, statistics of requests, information about performance.
LectureSetting up logging, error log, slow log.
PracticeConnecting log files. Logging of heavy requests. Log analyzers.
MySQL replication
LectureThe concept of replication: terminology, application features.
LectureTypes of replication.
LectureMaster-Slave replication.
LectureMaster-Master replication.
PracticeBuilding replication of the Master-Slave, Master-Master type.
MySQL forks
LecturePercona Server.
LectureXtraDB, Percona XtraBackup.
LectureMariaDB.
LectureReplication and sharding.
PracticePercona server installation and configuration MariaDB.
Tuning of the DBMS
LectureMyISAM tuning.
LectureInnoDB tuning.
LecturePercona XtraDB tuning.
PracticeMysqltuner. Bench tests. Load tests. Comparison of MySQL and Percona.
PostgreSQL
LecturePostgreSQL. General information.
LecturePostgreSQL directory and file structure.
LecturePostgreSQL commands.
LectureComparison of MySQL and PostgreSQL capabilities.
PracticeMysqltuner. Bench tests. Load tests. Comparison of MySQL and Percona. Installing PostgreSQL, creating databases, creating users. Installing PgAdmin.
PostgreSQL replication
LectureTypes of replication.
LectureMaster-Slave replication.
LectureMaster-Master replication.
PracticeBuilding replication of the Master-Slave, Master-Master type.
MySQL Security
Setting up user privileges.
LectureMySQL + SSL/TLS.
LectureStoring MySQL databases on an encrypted partition.
LectureRestricting connection to MySQL (iptables, unix socket files).
PracticeDatabases encryption, SSL connection to the server, connections limitation to MySQL on the iptables side.
MongoDB
LectureStructure of NoSQL Databases.
LectureMongoDB Data Types and Modeling.
LectureCollections and projection of MongoDB.
Indexation, aggregation, replication of MongoDB.
PracticeInstallation of MongoDB, creation and deleting databases, creation and deleting collections, setting up access to databases. Control Panels for MongoDB.
CassandraDB
LectureArchitecture and Terminology: cluster, keyspace, column family, super columns.
LectureOperations: mutation, get, multi_get, counters.
LectureCaching, key cache level, indexes, range scan, aggregation, scaling.
PracticeInstallation of Apache Cassandra. Config files and directives, cqlsh command line, creation of keyspace, tables creation, querying and retrieving data. Control panels for Apache Cassandra.
Project Work
ProjectSetting up a web server by technical task
Have you already seen our courses?
You will definitely find what you need π
At the end of the course the student will know
- basics of the SQL language, configuration files of database servers and types of tables
- system variables MySQL and its forks
- possible backup tools for PostgreSQL databases and its database management tools
- principle of the structure of NoSQL databases
- MongoDB's collection and protection, Cassandra architecture, operations and methods of scaling
At the end of the course the student will be able to
- install MySQL database servers and its forks;
- create users, databases and tables; manage access rights to databases
- set up backups and be able to restore databases/tables from backups
- set up MySQL event logs and its forks, get reports about the structure of tables and queries to databases
- set up MySQL and its forks' replication, analyze and optimize MySQL parameters
- set and configure MongoDB, create databases, manage collections and access to databases,
- install and configure Apache Cassandra
- work in the cqlsh command line, create tables, submit queries and get data,
- configure limited access to Cassandra databases, create users and assign them rights.