thieryl/MySQL_TIPS.md

Last active October 31, 2020 09:49

Star () You must be signed in to star a gist
Fork () You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/thieryl/7fd0fa78e4a09cd93b5ccbd77bb04d02.js"></script>
Save thieryl/7fd0fa78e4a09cd93b5ccbd77bb04d02 to your computer and use it in GitHub Desktop.

Download ZIP

MySQL - Cheatsheet

Raw

centos_mysql.md

Install a MySQL server on CentOS

MySQL is an open-source relational database that is free and widely used. It is a good choice if you know that you need a database but don’t know much about all of the available options.

This article describes a basic installation of a MySQL database server on CentOS Linux. You might need to install other packages to let applications use MySQL, like extensions for PHP. Check your application documentation for details.

Note: CentOS 7 has replaced MySQL with MariaDB. To reflect this, instructions for MariaDB procedures are included in this article.

Install the database application
Start and stop the database service
Start the mysql shell
Set the root password
View users
Create a database
Manage users and permissions
Summary

Install the database server

Follow the steps in this section to install the core database server.

Install MySQL

Install the MySQL database through the CentOS package manager (yum) by running the following commands at a command prompt:
```
 sudo yum install mysql-server
 sudo /sbin/service mysqld start
```

Run the following command:

 sudo /usr/bin/mysql_secure_installation

Press Enter to give no password for root when prompted for it.
To apply some reasonable security to your new MySQL server answer yes to all the prompts. In order, those prompts enable you set the root password, remove anonymous users, disable remote root logins, delete the test database that the installer included, and then reload the privileges so that your changes will take effect.

Install MariaDB

Install the MariaDB server through the CentOS package manager (yum) by running the following command at a command prompt:

sudo yum install mariadb-server mariadb

Allow remote access

If you have iptables enabled and want to connect to the MySQL database from another computer, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.

If you need to open a port, add the following rules in iptables to open port 3306:

iptables -I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

Note: If you edit the iptables rules file rather than using the command line to add rules, omit the iptables command at the beginning of each line when you add them to the file.

Start and stop the database service

After the installation is complete, you can start the database service by using the commands in this section. If the system is already started, a message informs you that the service is already running.

Start and stop MySQL

Use the following command to start MySQL:

sudo /sbin/service mysqld start

Use the following command to stop MySQL:

sudo /sbin/service mysqld stop

Start and stop MariaDB

Use the following command to start MariaDB:

sudo systemctl start mariadb.service

Use the following command to stop MariaDB:

sudo systemctl stop mariadb.service

Launch at reboot

To ensure that the database server launches after a reboot, you must enable the chkconfig utility. Use the following commands to do this.

Enable chkconfig on MySQL

sudo chkconfig mysqld on

Enable chkconfig on MariaDB

sudo systemctl enable mariadb.service

Start the mysql shell

There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach: the mysql shell.

At the command prompt, run the following command to launch the mysql shell and enter it as the root user:
```
 /usr/bin/mysql -u root -p
```
When you’re prompted for a password, enter the one that you set at installation or, if you haven’t set one, press Enter to submit no password.

The following mysql shell prompt should appear:

    mysql>

Set the root password

Because you have just installed the MySQL database server, the root account within MySQL has no password set yet. If you are logged in to the database server, set the root password by running the following command:

/usr/bin/mysqladmin -u root password 'new-password'

If you are not logged in to the database server you can remotely set the root password by specifying the hostname of your database server:

/usr/bin/mysqladmin -u root --password='new-password' -h hostname-of-your-server 'new-password'

Note: The rest of this article shows SQL commands in all capitals, but you can also type them in lowercase. The commands are shown capitalized by convention, to make them stand out from field names and other data.

View users

MySQL stores user information in its own database. The name of the database is mysql. Inside that database, the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:

SELECT User, Host, Password FROM mysql.user;

The following list describes the parts of that command:

SELECT tells MySQL that you are asking for data.
User, Host, Password tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.
FROM mysql.user tells MySQL to get the data from the mysql database and the user table.
; (a semicolon) ends the command.

Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.

User hosts

Following is example output for the preceding query:

SELECT User, Host, Password FROM mysql.user;
+------------------+-----------+------------------------------------------+
| User             | Host      | Password                                 |
+------------------+-----------+------------------------------------------+
| root             | localhost | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | demohost  | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | 127.0.0.1 | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
|                  | %         |                                          |
+------------------+-----------+------------------------------------------+

Users are associated with a host, specifically the host to which they connect. The root user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server (demohost in this example). You usually need to set a user for only one host, the one from which you typically connect.

If you’re running your application on the same computer as the MySQL server, the host that it connects to by default is localhost. Any new users that you create must have localhost in their host field.

If your application connects remotely, the host entry that MySQL looks for is the IP address or DNS hostname of the remote computer (the one from which the client is coming).

A special value for the host is %, as you can see in the preceding output for the blank, or anonymous, user (see the following section). The % symbol is a wildcard that applies to any host value.

Anonymous users

In the example output, one entry has a host value but no username or password. That’s an anonymous user. When a client connects with no username specified, it’s trying to connect as an anonymous user.

You usually don’t want any anonymous users, but some MySQL installations include one by default. If you see one, you should either delete the user (refer to the username with empty quotes, like ‘’) or set a password for it.

Create a database

There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning that it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.

Some applications create a database as part of their setup process, but others require you to create a database and tell the application about it.

To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:

CREATE DATABASE demodb;

The database is created. You can verify its creation by running a query to list all databases. The following example shows the query and example output:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demodb             |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Manage users and privileges

Use the instructions in this section to add users for the database and grant and revoke privileges.

Add users and privileges

When applications connect to the database using the root user, they usually have more privileges than they need. You can create a new user that applications can use to connect to the new database. In the following example, a user named demouser is created.

To create a new user, run the following command in the mysql shell:

CREATE USER 'demouser'@'localhost' IDENTIFIED BY 'demopassword';

You can verify that the user was created by running a SELECT query again:

SELECT User, Host, Password FROM mysql.user;
+----------+-----------+------------------------------------------+
| User     | Host      | Password                                 |
+----------+-----------+------------------------------------------+
| root     | localhost | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root     | demohost  | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root     | 127.0.0.1 | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| demouser | localhost | 0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6 |
+----------+-----------+------------------------------------------+

Grant database user privileges

Right after you create a new user, it has no privileges. The user can be used to log in to MySQL, but it can’t be used to make any database changes.

Give the user full privileges for your new database by running the following command:
```
 GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
```
Flush the privileges to make the change take effect.
```
 FLUSH PRIVILEGES;
```

To verify that the privileges were set, run the following command:

 SHOW GRANTS FOR 'demouser'@'localhost';

MySQL returns the commands needed to reproduce that user’s privileges if you were to rebuild the server. The USAGE on \*.\* part means that the user gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.

 +-----------------------------------------------------------------------------------------------------------------+
 | Grants for demouser@localhost                                                                                   |
 +-----------------------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |
 | GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |
 +-----------------------------------------------------------------------------------------------------------------+
 2 rows in set (0.00 sec)

Revoke privileges

Sometimes you might need to revoke (remove) privileges from a user. For example: suppose that you were granting ALL privileges to ‘demouser’@’localhost’, but you accidentally granted privileges to all other databases, too:

+-----------------------------------------------------------------------------------------------------------------+
| Grants for demouser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |
| GRANT ALL PRIVILEGES ON *.* TO 'demouser'@'localhost'                                                           |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

To correct the mistake, you can use a REVOKE statement, followed by GRANT statement to apply the correct privileges.

REVOKE ALL ON *.* FROM demouser@localhost;
GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
SHOW GRANTS FOR 'demouser'@'localhost';

+-----------------------------------------------------------------------------------------------------------------+
| Grants for demouser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |
| GRANT ALL PRIVILEGES ON 'demodb'TO 'demouser'@'localhost'                                                           |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now your user has the correct privileges, and therefore your database server is slightly more secure (granting privileges like ALL on *.* is deemed as a very bad practice). You should also read official MySQL documentation regarding possible privilege choices, to grant only those privileges truly needed, rather than using ALL.

Summary

If you’re just creating a database and a user, you are done. The concepts covered here should give you a solid grounding from which to learn more.

Raw

centos_mysql_setup.md

Configure MySQL server on CentOS

In the previous article we covered a basic MySQL server setup on CentOS Linux. We set the root password, created a database, and created a user for the database. Now let’s look at MySQL in a little more detail so we can tweak its configuration and be ready in case something goes wrong.

Finding the config files

By default you’ll find MySQL’s configuration file at:

/etc/my.cnf

If it’s not there, however, you can ask mysqld where it looks for its config. Run the command:

/usr/libexec/mysqld --help --verbose

You’ll get a flood of text back. The first part describes the options you can send to the server when you launch it. The second part is all the configuration stuff that was set when the server was compiled.

What we’re looking for shows up near the start of the output. Find a couple lines that look like:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

And there we are. The server works down that list until it finds a configuration file.

my.cnf

With the location in hand, open the my.cnf file and have a look inside.

/etc/my.cnf

Any lines starting with “#” are comments, and they mostly document what the different settings are for. They’re good to read through. You’ll find details like the location of log files and where the database files are kept.

Config groups

There are lines in the config file that just contain a word in square brackets, like “[client]” or “[mysqld]”. Those are “config groups” and they tell the programs that read the configuration file which parts they should pay attention to.

See, while we’ve been focusing on the server part of MySQL, it’s technically a collection of tools. That includes the server (mysqld), the client (mysql), and some other tools we’ll talk about in a bit. Those programs look in my.cnf to see how they should behave.

There’s a bit more to it, but basically: The “client” config section controls the mysql client, and the “mysqld” section controls the server config.

Log files

If something does go wrong the best place to start troubleshooting any program is its logs. By default MySQL stores its log files in the directory:

/var/log

You may need to use sudo to get a listing of the files in that directory.

If you don’t find the MySQL logs in the default directory you’ll need to check MySQL’s config. Look in the my.cnf file and look for a “log_error” line, as in:

log_error = /var/log/mysql/error.log

If you don’t see a line like that, create one in the “mysqld” section so MySQL will use its own error log. We recommend using the location in the example, creating the “/var/log/mysql” directory if it doesn’t already exist. Then restart MySQL to make the change.

Make sure the log directory you choose can be written to by the user controlling the MySQL process (usually “mysql”). The user running the process will be defined in the “user” config value for mysqld in my.cnf.

Network settings

There might be a “port” setting under both the client and server config sections. The port under the server section controls what port the server will listen to. By default that’s 3306 but you can change it to anything you’d like.

The port in the client section tells the client what port to connect to by default. You’ll generally want the two port settings to match up.

If you don’t see the port entries in the config file that just means they’re using the default. If you want to change the port you would add the lines in the appropriate categories:

[client]
port = 3306

[mysqld]
port = 3306

The other network setting to look for is the “bind-address” value. That usually gets set to the address for localhost, 127.0.0.1. By binding to localhost the server makes sure no one can connect to it from outside the local machine.

If you’re running your MySQL server on a different machine from your application you’ll want to bind to a remotely-accessible address instead of localhost. Change the bind-address setting to match your public IP address (or, better, a backend IP address on a network that fewer machines can access).

If you don’t see a “bind-address” entry you should put one into the “mysqld” category to help control access to the server:

[mysqld]
bind-address = 127.0.0.1

Remember to account for the client’s hostname when you set up your database users and to poke a hole in your firewall if you’re running iptables.

mysqld and mysqld_safe

Behind the scenes there are actually two versions of the MySQL server, “mysqld” and “mysqld_safe”. Both read the same config sections. The main difference is that mysqld_safe launches with a few more safety features enabled to make it easier to recover from a crash or other problem.

Both mysqld and mysqld_safe will read config entries in the “mysqld” section. If you include a “mysqld_safe” section, then only mysqld_safe will read those values in.

By default the mysql service launches “mysqld_safe”. That’s a good thing, and you should only look to change that if you really know what you’re doing.

mysqladmin

The mysqladmin tool lets you perform some administrative functions from the command line. We won’t talk much about it here because we’re just trying to get you up and running with enough basics to get by. It’s worth looking at the tool in more depth later to see what it can do, particularly if you need to build scripts that perform functions like checking the status of the server or creating and dropping databases.

Backups

You have a few options when it comes to making backups of your databases apart from the usual “back up the whole machine” approach. The main two are copying the database files and using mysqldump.

File copy

By default MySQL creates a directory for each database in its data directory:

/var/lib/mysql

Once you’ve found the data directory, hold off a moment before making a copy of it. When the database server is active it could be writing new values to tables at any time. That means if it writes to a table halfway through your copy some files will change and lead to a corrupt backup. Not a good thing if you’re trying to plan for disaster recovery.

To make sure the database files are copied cleanly you can shut the MySQL server down entirely before the copy. That’s safe but isn’t always ideal.

Another approach you can take is to lock the database as read-only for the duration of the copy. Then when you’re done, release the lock. That way your applications can still read data while you’re backing up files.

Lock the databases to read-only by running, from the command line:

mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

To unlock the database when you’re done, run:

mysql -u root -p -e "UNLOCK TABLES;"

We’re using a new option with the mysql client, “-e”. That tells the client to run the query in quotes as if we’d entered it in the mysql shell proper.

Note that if you’re setting these commands up in a script you can put the password in quotes right after “-p” with no space between the two, as in:

mysql -u root -p"password" -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p"password" -e "UNLOCK TABLES;"

Just make sure you set the permissions on that file to restrict read access. We don’t want just anyone to be able to see that password.

mysqldump

Another approach to backing up your database is to use the “mysqldump” tool. Rather than copying the database files directly, mysqldump generates a text file that represents the database. By default the text file contains a list of SQL statements you would use to recreate the database, but you can also export the database in another format like CSV or XML. You can read the man page for mysqldump to see all its options.

The statements generated by mysqldump go straight to standard output. You’ll want to specify a file to redirect the output to when you run it. For example:

mysqldump -u root -p demodb > dbbackup.sql

That command will tell mysqldump to recreate the “demodb” database in SQL statements and to write them to the file “dbbackup.sql”. Note that the username and password options function the same as the mysql client, so you can include the password directly after “-p” in a script.

Restore from mysqldump

Restoring a mysqldumped database looks similar to what was used to create it, but we use plain old “mysql” instead of “mysqldump”:

mysql -u root -p demodb < dbbackup.sql

We also change from a greater-than to a less-than sign. That switches the command from redirecting its output to telling it to read its input from the existing file. That input is sent to the “mysql” command, causing the mysqldumped instructions to recreate the database.

Note that by default the SQL statements generated would just add to existing database tables, not overwrite them. If you’re restoring a backup over an existing database you should drop the database’s tables first, or drop and recreate the database itself. You can change that behavior by using the “–add-drop-table” option with the command that creates the mysqldump. That causes mysqldump to add a command to the backup files it writes that will drop tables before recreating them.

Database engine

The last concept we’ll talk about here is that of the “database engine”. The engine is the process that’s churning away behind the scenes, writing to and reading data from files. You won’t usually need to know anything other than that it’s there, but sometimes you’ll want to run an application that’s been optimized for a particular database engine.

The engine type is set when a table is created. Tables are usually created by the application that’s going to use them, which is why we aren’t going to get into that syntax here.

To see the engine used by your database’s tables you can run the following command in the MySQL shell:

SHOW TABLE STATUS FROM demodb;

Change “demodb” to the name of your database.

Choosing an engine

Ideally you won’t need to choose an engine. If you’re not very familiar with MySQL that’s certainly the safest way to go - let the application do its thing, and if you’re writing the application, use the default engine until you’re more comfortable with your options.

If you have a database administrator, do whatever he or she says. They’re smart people, they know what they’re talking about.

The two database engines used most often with MySQL are “MyISAM” and “InnoDB”. The default database engine for MySQL version 5.1 and earlier is MyISAM, while InnoDB is the default database engine starting with MySQL version 5.5.

MyISAM

Because MyISAM has been the default in MySQL for a while it’s the most compatible choice of the two main engines. Certain types of searches perform better on MyISAM than InnoDB. Just because it’s the older of the two doesn’t mean it can’t be the best for a given application type.

InnoDB

InnoDB is more fault-tolerant than MyISAM and handles crashes and recovery with a much smaller chance of database corruption. This is a good thing.

The main trouble with InnoDB is that for best performance it requires a lot of tweaking for your environment and access patterns. If you have a DBA that’s no problem, but if you’re a developer who just wants a database up and running for a test server you probably won’t want to deal with tuning InnoDB.

It’s possible you may be running an application that requires InnoDB, and if you’re using MySQL 5.1 or earlier there might not be any settings already in the my.cnf config file. That can be a problem if you’re running on a server that doesn’t have an abundance of memory.

Some settings to get you started with InnoDB on a shared server with 256 megs of RAM are:

innodb_buffer_pool_size = 32M
innodb_log_file_size = 8M
innodb_thread_concurrency = 8
innodb_file_per_table

Add those to the [mysqld] section of the config file. Again, those are only rough guides - enough to get you running, but definitely not optimized. For that you’ll probably want a DBA, or at least to experiment with incremental changes over time.

Summary

Now you should have MySQL configured for your environment, and might even have accounted for the database engine being used by your tables.

Raw

mysql_cheat_cheet.md

MySQL Cheat Sheet

====

MySQL cheat sheet provides you with the most commonly used MySQL commands and statements that help you work with MySQL more effectively.

Access MySQL server from the mysql client using a username and password (MySQL will prompt for a password):

mysql -u [username] -p;

Access a specific database using a username and password:

mysql -u [username] -p [database];

Export data using mysqldump tool

mysqldump -u [username] -p [database] > data_backup.sql;

To clear MySQL screen console window on Linux, you use the following command:

mysql> system clear;

Currently, there is no command available on Windows OS for clearing MySQL screen console window.

Working with Database

Create a database with a specified name if it does not exist in database server

CREATE DATABASE [IF NOT EXISTS] database_name;

Use database or change current database to another database you are working with

USE database_name;

Drop a database with specified name permanently. All physical file associated with the database is no longer exists.

DROP DATABASE [IF EXISTS] database_name;

Show all available databases in the MySQL database server

show databases;

Working with Table

Lists all tables in a current database.

show tables;

Create a new table or a temporary table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table(
   key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   ...
);

Altering table structure. There are many actions that you can use with the ALTER TABLE statement as follows:

Add a new column into a table

ALTER TABLE table ADD [COLUMN];

Drop an existing column in a table

ALTER TABLE table DROP [COLUMN];

Add index with a specific name to a table on a column.

ALTER TABLE table ADD INDEX [name](column, ...);

Add primary key into a table.

ALTER TABLE table ADD PRIMARY KEY (column,...)

Remove primary key from a table.

ALTER TABLE table DROP PRIMARY KEY

Deleting table structure and data permanently.

 DROP TABLE [IF EXISTS] table [, name2, ...]
 [RESTRICT | CASCADE]

Get information about th table or a column.

DESCRIBE table [column]

Working with Index

Creating an index with the specified name on a table

CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table (column,...)

Removing a specified index from table

DROP INDEX index_name

Querying Data

Query all data from a table

SELECT * FROM table

Query specified data which is shown in the column list from a table

SELECT column, column2….
FROM table;

Query unique records

SELECT DISTINCT (column)
FROM table;

Query data with a filter using a WHERE clause.

SELECT *
FROM table
WHERE condition;

Change the output of the column name using column alias.

SELECT column_1 AS new_column_1, ...
FROM table

Query data from multiple tables using inner join or left join

SELECT * 
FROM table_1
INNER JOIN table_2 ON conditions

SELECT * 
FROM table1
LEFT JOIN table2 ON conditions


SELECT COUNT (*)
FROM table

Sorting ascending or descending based on one or more columns using ORDER BY clause.

SELECT column, column2, ...
FROM table
ORDER BY column ASC [DESC], column2 ASC [DESC],...;

Group rows using GROUP BY clause.

SELECT *
FROM table
GROUP BY column_1, column_2, ...;

Filter group of rows using both GROUP BY and HAVING clauses.

SELECT *
FROM table
GROUP BY column_1
HAVING condition;

Modifying Data

Insert a new row into a table

INSERT INTO table(column1,column2,...)
VALUES(value_1,value_2,...);

Insert multiple rows into a table

INSERT INTO table(column1,column2,...)
VALUES(value_1,value_2,...),
      (value_1,value_2,...),
      (value_1,value_2,...)...

Update data for all rows

UPDATE table
SET column_1 = value_1,
    ...

Update data for a set of rows specified by a condition in WHERE clause.

UPDATE table
SET column_1 = value_1,
    ...
WHERE condition

Update with join

UPDATE table_1, table_2
INNER JOIN table_1 ON table_1.column_1 = table_2.column_1
SET column_1 = value_1,
WHERE condition

Delete all rows in a table

DELETE FROM table;

Delete rows specified by a condition

DELETE FROM table
WHERE condition;

Delete with join

DELETE table_1, table2
FROM table_1
INNER JOIN table_2 ON table_1.column_1 = table_2.column_2
WHERE condition;

Search

Search for data using LIKE operator:

SELECT * FROM table
WHERE column LIKE '%value%'

Text search using a regular expression with RLIKE operator.

SELECT * FROM table
WHERE column RLIKE 'regular_expression'

Raw

MySQL_TIPS.md

MySQL

Getting started:

Commands

Access monitor: mysql -u [username] -p; (will prompt for password)

Show all databases: show databases;

Access database: mysql -u [username] -p [database] (will prompt for password)

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records: SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';

Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: truncate table [table];

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: DROP TABLE [table];

Deleting databases: DROP DATABASE [database];

Custom column output names: SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql

Use --lock-tables=false option for locked tables (more info here).

Import a database dump (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout: exit;

Backup and Restore

Dump The MySQL Table

mysqldump -u username -p -r my_output.sql my_database

Compress Output with 7zip using the PPMd Algorithm

7z a -t7z my_output.7z my_output.sql -m0=PPMd

Decompress Output with 7zip

7z e my_output.7z

Restore The MySQL Table

mysql -u username -p my_database < my_output.sql

Aggregate functions

Select but without duplicates: SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate total number of records: SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]: SELECT MAX([column]) FROM [table];

Get smallest value: SELECT MIN([column]) FROM [table];

Get average value: SELECT AVG([column]) FROM [table];

Get rounded average value and group by [category-column]: SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple tables

Select from multiple tables: SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine rows from different tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)

Rename column or table using an alias: SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

List all users: SELECT User,Host FROM mysql.user;

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'; (source)

Raw

mysql_tuning.md

INSTALLING PERCONA SERVER 5.6 FROM BINARIES

Before installing, you might want to read the Percona Server 5.6 Release notes http://www.percona.com/doc/percona-server/5.6/release-notes/release-notes_index.html

./percona_install.sh

MySQL Tuning

##############################################################################################
##  http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html                           ##
##  https://raw.githubusercontent.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl     ##
##  https://launchpadlibrarian.net/78745738/tuning-primer.sh                                ##
##  yum install mytop  /  apt-get install mytop                                             ##
##############################################################################################

[mysqld]

### MyISAM #
key_buffer_size = 16M # keep it low if no myisam data
myisam-recover-options = FORCE,BACKUP

### SAFETY #
innodb = force
max_allowed_packet = 250M
max_connect_errors = 100000
bind-address = 127.0.0.1
skip-name-resolve

### LANGUAGE #
#init_connect='SET collation_connection = utf8_unicode_ci'
#init_connect='SET NAMES utf8'
#character-set-server=utf8
#collation-server=utf8_unicode_ci
#skip-character-set-client-handshake

### CACHES AND LIMITS #
back_log = 20
interactive_timeout = 7200
wait_timeout = 7200
net_read_timeout = 120
net_write_timeout = 300
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 128M
max_heap_table_size = 128M
#query_cache_type = 2
#query_cache_size = 128M
max_connections = 150
thread_cache_size = 32
thread_pool_size = 16
open_files_limit = 65535
table_definition_cache = 4000
table_open_cache = 4000

### INNODB_ #
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 7200
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 4G

### LOGGING #
#log_error = /var/log/mysql/mysql-error.log
#log_queries_not_using_indexes = 1
#log_slow_admin_statements = 1
#slow_query_log = 1
#long_query_time = 1
#slow_query_log_file = /var/log/mysql/mysql-slow.log

### BINARY LOGGING #
#log_bin = /var/lib/mysql/mysql-bin
#expire_logs_days = 14
#sync_binlog = 1

Raw

postgresql.md

PostgreSQL 9.5: A quick start on Fedora

By Thiery Louison

(file/TDD/postgresql-quick-start-f24-945x400.png)

PostgreSQL is one of the most popular object-relational database management system (shortened to ORDBMS) and is 100% open-source. It is not purely about relations anymore: PostgreSQL is more and more about NoSQL as well. The following article is a short tutorial to set up PostgreSQL 9.5 on Fedora, so it can be used for a development environment. For a production deployment, it is recommended to use a different set-up and harden the service.

The set of PostgreSQL database packages in Fedora’s stable repositories are almost identical to the upstream set of RPMs. There are client tools in the postgresql package. The client library in the postgresql-libs package is often required by various connectors. The most important part of the database, the daemon, is available in the postgresql-server package. Some more server-side extensions, tools, or supporting packages may be listed by running the following command in a terminal.

$ dnf list postgresql\\*

Basic deployment examples may be found in the article on the Fedora wiki as well. Some first steps are also described in the Fedora Developer Portal.

Basic PostgreSQL setup

Start by installing the packages, initializing the data directory, and starting the daemon.

$ sudo dnf install postgresql-server
$ sudo postgresql-setup --initdb
$ sudo systemctl start postgresql

Now, connect using the superuser by switching to postgres user via su. Set a password for this superuser.

$ su - postgres
$ psql
 psql (9.5.3)
 Type "help" for help.

 postgres=# \\password postgres

Creating a user and a database

It’s not a good idea to connect to the database as postgres superuser from applications (like you don’t work as the root user in Linux all the time). For that, we’ll need a database and a separate user to access the database. Create them with the following commands.

$ createuser john -P
$ createdb --owner=john mydb

We also want to limit the connections to the server from localhost only. Edit /var/lib/pgsql/data/pg_hba.conf to look like the following example below to do this.

\# TYPE DATABASE USER     ADDRESS      METHOD
 host  all      all      127.0.0.1/32 md5
 host  all      all      ::1/128      md5
 local all      postgres              peer

This configuration allows all users that provide the password (specific to PostgreSQL) to connect from the localhost. It allows the postgres user (a.k.a. the superuser) to connect in case the same user is authenticated in the operating system (su - postgres). More about this is detailed in the upstream documentation.

Now we can restart the PostgreSQL server so the changed configuration applies.

$ sudo systemctl restart postgresql

We’re all set to use the john user to access the database mydb now.

$ psql -h localhost -U john mydb
Password for user john: 
psql (9.5.3)
Type "help" for help.

mydb=> _

At this point, you can work with the database as you need: create tables, fill then with data, and so on.

PostgreSQL in the container

Linux containers (especially Docker) are slowly approaching production systems. It is also not surprising there is a PostgreSQL Docker image provided by Fedora. The source is found in the Fedora-dockerfiles repository. The image is found in fedora/postgresql on Docker Hub. Starting a container for serving PostgreSQL without touching the rest of the system is easy.

Install and run the Docker daemon.

$ sudo dnf install docker
$ sudo systemctl start docker

Pull the image.

$ sudo docker pull fedora/postgresql

Prepare directory for data.

$ sudo mkdir data
$ sudo chown 26:26 data
$ sudo chcon -t svirt\_sandbox\_file_t data

Start the container with a few arguments. The container uses the prepared directory to store data into and creates a user and database.

$ sudo docker run -v "\`pwd\`/data:/var/lib/pgsql/data:Z" -e POSTGRESQL\_USER=john -e POSTGRESQL\_PASSWORD=secret -e POSTGRESQL_DATABASE=mydb -d -p 5432:5432 fedora/postgresql

Now you have PostgreSQL running as a container while storing data into the data directory in the current working directory.

Have some feedback?

That’s all for now! We are happy to hear your experiences with PostgreSQL on Fedora. Did you experience any difficulties? Would you like more versions (not only the latest one)? Anything else regarding the PostgreSQL or different databases? Feel free to use the comments here or approach me directly.