MYSQL Config Editor
mysql_config_editor:
❖ Configure Authentication information for connecting to MySQL server
❖ Stores authentication credentials in an obfuscated login path file called .[Link] -
Encrypted
❖ Location: user’s home directory - Syntax: mysql_config_editor set –login-path=client –
host= –user= –password
❖ login-path is option group that specify which MySQL server to connect and which
account to auth
❖ By default mysql client reads [client] and [mysql] groups
.[Link]:
[client]
user = root
password =
host = localhost
[prod]
user = bob
MySQL Config Editor
ASSIGNMENT: Configure MySQL account to save credentials in
encrypted format so user can login to MySQL without password
ASSIGNEE: Bob, The Junior DBA
Syntax:
❖ mysql_config_editor set login-path=client –user=root –
password
MYSQL Admin Program
mysqladmin:
❖ MySQL Server Administration program
❖ Client for performing administrative operations:
➢ shutdown
➢ create <database_name>
➢ current status
➢ ping if MySQL is alive
➢ Start Replica
➢ Stop Replica
❖ Syntax: mysqladmin options command
Example:
❖ mysqladmin status
❖ mysqladmin ping
❖ mysqladmin create database
❖ mysqladmin drop database
MySQL Admin Program
ASSIGNMENT: Create Database - employees
ASSIGNEE: Bob, The Junior DBA
Syntax:
❖ mysqladmin create employees
MYSQL Execute SQL
Files
source:
❖ From within mysql shell - using \. or source
❖ mysql> source [Link] or mysql> \. [Link]
mysql:
❖ By running mysql client program and accepting .sql file as input
❖ mysql –host=host_name –user=user_name –password= database_name < [Link]
shell script:
❖ By creating an executable shell script and executing it
❖ mysql –host=host_name database_name < $1
pipe method:
❖ cat [Link] | mysql
MySQL Admin Program
ASSIGNMENT: Execute [Link] - Create staff table in
employees db
ASSIGNEE: Bob, The Junior DBA
Syntax:
❖ mysql>source [Link]
❖ mysql –host=localhost employees < [Link]
❖ bash [Link] [Link]
❖ cat [Link] | mysql
MYSQL mysqlimport
mysqlimport:
❖ mysqlimport is a data import program
❖ Takes .txt with tab-delimited file as input
Syntax:
❖ mysqlimport [options] database [Link] [[Link]] …
Import Data Directory Configuration:
❖ secure_file_priv - denoted a directory from which data files can be
loaded
MySQL mysqlimport
ASSIGNMENT: Load data from [Link] - Populate staff table in
employees db
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Bob will use mysqlimport utility
❖ Fetch secure_file_priv value
❖ Copy file and change permissions
❖ Load Data mysqlimport [options] db_name
${secure_file_priv}/[Link]
MYSQL mysqlcheck
mysqlcheck:
❖ mysqlcheck is a table maintenance program
❖ It checks, repairs, optimize, or analyze tables
❖ Table name as input
Note:
❖ Table will be locked while mysqlcheck is running - no db operations
Syntax:
❖ myqlcheck [options] db_name table_name
MySQL mysqlcheck
ASSIGNMENT: Check the integrity of staff table
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Bob will use mysqlcheck utility
❖ mysqlcheck employees staff
MYSQL mysqlshow
mysqlshow:
❖ Display database, table, and column information
❖ Takes database name and table name as input
Syntax:
❖ myqlshow [options] db_name table_name
❖ mysqlshow [options] db_name table_name [column_name]
MySQL mysqlshow
ASSIGNMENT: Print out staff table information
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Bob will use mysqlshow utility
MYSQL Timezone Data
mysql_tzinfo_to_sql:
❖ Loads the time zone data from zoneinfo database into system mysql database
❖ Zoneinfo database is actually zone files that describe time zones
❖ Typical location on Linux is /usr/share/zoneinfo
Timezone Tables:
❖ Time_zone
❖ Time_zone_name
❖ Time_zone_transition
❖ Time_zone_transition_type
❖ time_zone_leap_second
Syntax:
❖ mysql_tzinfo_to_sql zoneinfo_database | mysql [options] db_name
MySQL Timezone Data
ASSIGNMENT: Load Timezone Data into MySQL
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Bob will use mysql_tzinfo_to_sql utility
❖ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql
MYSQL Example
Databases
MySQL Example Databases:
❖ Free to download and use
Example Databases:
❖ employees
❖ world
❖ sakila
URL Information:
❖ [Link]
MySQL Example
Databases
ASSIGNMENT: Download world example database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Bob will download [Link] file
❖ Unzip world-db file
❖ Execute [Link] with mysql client
BOB PROGRESS
CHECKPOINT
Bob’s Knowledge Gain:
❖ Authentication Credentials - mysql_config_editor
❖ CREATE or DROP DATABASE - mysqladmin
❖ Execute .sql File
❖ Import data into MySQL - mysqlimport
❖ Integrity of Tables - mysqlcheck
❖ Time Zone Data - mysql_tzinfo_to_sql
❖ Example Databases
❖ Read Binary Logs - mysqlbinlog