General MariaDB and MySQL

Where is my my.cnf?

Your my.cnf for the system will be at:

/etc/my.cnf

Which usually does include a files from /etc/my.cnf.d/ directory.

DirectAdmin does create a secondary my.cnf, but it's only used for the mysqldump calls. It's found at

/usr/local/directadmin/conf/my.cnf

and should not be changed as it's overwritten regularly (based on the mysql.conf in the same directory).

Apart from those 2 my.cnf files, you should not have any my.cnf files anywhere else.

Common "bad" my.cnf files are:

/etc/mysql/my.cnf

You shouldn't have this file. You shouldn't even have a /etc/mysql directory, as it will conflict with your /etc/my.cnf.

/root/.my.cnf

This should not exist as our call to mysqldump will find it, which breaks our calls to mysqldump if it contains a user/password.

Of your 2 my.cnf files, only /usr/local/directadmin/conf/my.cnf should have a user/password value. No other my.cnf file should have a user/pass set within it.

local-infile=0 in your /etc/my.cnf

For security reasons, it's often a good idea to have:

[mysqld]
local-infile=0

present in your system my.cnf file.

If you want to set all users except da_admin and root to not have that privilege at the mysql level, then login to mysql with the login/pass from /usr/local/directadmin/conf/mysql.conf, and run the query:

use mysql;
UPDATE mysql.user SET File_priv='N' WHERE user!='da_admin' AND user!='root';
FLUSH PRIVILEGES;
quit

NOTE that the above command will remove file access from all accounts on your system, except da_admin and root, so just be aware that it's a global change.

How to upgrade MySQL / MariaDB with CustomBuild 2.0

To upgrade mysql using the CustomBuild script, adjust the following commands to the desired database type and version:

cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build set "mysql" 5.7
./build set mysql_inst "mysql"
./build "mysql"

Where MySQL can be 5.7 or 8.0 .

Where MariaDB can be 10.3, 10.4, 10.5 or 10.6 .

In place replacement between MariaDB and MySQL is no longer supported since the two DBMS have diverged too much.

If you have mysql_backup=yes set set in options.conf file then a full raw SQL backup will be run prior to the upgrade. It goes without saying, always make backups, either with this tool or via other means. It makes sense to perform backup just once, next disable it during the upgrade by multiple hops.

Connect to your MySQL database from a remote connection

If you have a MySQL database with your hosting account and need to connect to it from your home computer, or another web server, you'll need to add a remote "Access Host" to your database to allow the connection in.

Go to:

User Level -> MySQL Management -> databasename -> Add Access Host

You can either add the IP of the remote connecting box, or just use:

to allow any IP (if you're unsure of the remote IP, or if it may change).

Note that the correct login/password is still required, the Access Host is just another layer of security.

Also make sure that port 3306 is open in your firewall on the DirectAdmin box, so the remote box can connect.

Setting up DA to use a remote MySQL server

If you host a large database that can't be split up, and your server doesn't have the resources to handle it, you can set up MySQL to be run on an external server.

This shouldn't be needed too often, as usually, you can just move entire user accounts to another server to ease the load. But in the case of one large database using up the whole server, then you don't have much choice.

Note that this guide does not transfer any databases over.

It's generally a good idea to do this before you add users.

Also, MySQL will continue to run on your local DA, so existing databases and scripts should continue to function, however users will not be able to control them through DA.

The first step is to install MySQL onto the remote server. DA is not require on this server, as it is just used for MySQL and nothing else. I won't go into the detail on how to do this (use Google).
The next step is to set up a user that DA can connect to on the remote MySQL server. The default DA user is da_admin, so staying with that name is a good idea, but not required.

You can use this guide to set up the da_admin user on your server.

The username and password you set up have to be set in the /usr/local/directadmin/conf/mysql.conf file for DA to use.

On a related note, if the remote server is on a LAN IP and the connecting IP to that remote server will not be your server IP (but rather some other IP such as a 192.168.x.x type of IP), then you can use this guideopen in new window to specify a default access host instead of the server IP.

Note that the above guide will only grants da_admin on the "localhost", meaning you have to already be on that remote mysql server to use the account. We'll need to add another access host (ip) to allow the DA server to connect to it.

Basically, you just run the "GRANT ALL PRIVILEGES ON . TO da_admin@localhost" command again, but you change localhost to the IP of your DA server:

GRANT ALL PRIVILEGES ON *.* TO da_admin@1.2.3.4 WITH GRANT OPTION;
FLUSH PRIVILEGES;

we don't need the "identified by 'pass'" again, because the password is set already from adding it the first time.

At this point, the remote MySQL database should be set up and ready to accept DA to use it. Test it out by logging into your DA machine via SSH, and type:

mysql -uda_admin -p --host=4.3.2.1

where 4.3.2.1 is the IP of your MySQL server. If you can't connect, go back over your settings and also make sure that port 3306 is open on the remote box as well as outgoing in any firewalls you may be using on the local box.

Now, the easier part is to tell DA to use the remote server. Edit the /usr/local/directadmin/conf/mysql.conf file and add the line:

host=4.3.2.1

where 4.3.2.1 is the IP of your remote server. Make sure there is a newline character at the end of the line (e.g., press enter).

That should be it. Log in to DA and check the MySQL section of your user level to see if you get any errors. If not, try adding a database to make sure it works.
The only cleanup task would then be to fix up phpMyAdmin to also connect to the remote host.

Edit the /var/www/html/phpMyAdmin/config.inc.php file , find this line:

$cfg['Servers'][$i]['host']          = 'localhost'; // MySQL hostname or IP address

and change it to

$cfg['Servers'][$i]['host']          = '4.3.2.1'; // MySQL hostname or IP address

where 4.3.2.1 is the IP of your mysql server. Use proper customization for the config file.

For new MySQL User databases, you'll want to tell DirectAdmin about the new IP, so they have that IP assigned into their access hosts. Set the "host" value in the mysql.conf for remote database controlopen in new window or add multiple access_host values upon DB creationopen in new window.

Multiple access_hosts could be specified in mysql.conf file:

access_host=1.2.3.4
access_host1=2.3.4.5
access_host2=3.4.5.6

where the following are required:

the host= value must be set to some value other than localhost. (usually the IP of a remote box).
the first access_host= entry must already exist. If you don't have the first one, then the numbered values won't be loaded.
the number you use after the access_hostX= value doesn't matter.. it can be any number, any order, out of order, eg: access_host76345834=1.2.3.4 is allowed.

Any database created with these values set will have the listed access_hosts added to their database (eg, 1.2.3.4, 2.3.4.5, 3.4.5.6). This also applies to the restores of the databases, even if the backed up tar.gz DBs do not not have any access_hosts listed in the backup.

NOTE:

As of DirectAdmin 1.57.0, you can install DirectAdmin without MySQL, allowing you to specify a remote box ahead of time: https://www.directadmin.com/features.php?id=2351open in new window

Converting from MySQL 5.5 to MariaDB 5.5

If you're running MySQL 5.5 and wish to convert to MariaDB 5.5 (which then allows a higher MariaDB later on), then you can do the following:

Make full DA User backups, and .sql backups before you do anything else. Ensure the backups are okay and ready for restore, in case anything goes wrong. To create the .sql backups, type:

cd /usr/local/directadmin/custombuild/
da build set mysql_backup yes
da build mysql_backup

wait for the backup to complete, then rename it to a unique folder so it's not overwritten:

mv mysql_backups mysql_backups.`date +%F`
da build set mysql_backup no

Make a copy of the /var/lib/mysql folder (/home/mysql on Debian). We'll stop mysqld first, to avoid corruption during the copy.

perl -pi -e 's/mysqld=ON/mysqld=OFF/' /usr/local/directadmin/data/admin/services.status
systemctl stop mysqld
cd /var/lib
cp -Rp mysql mysql.conversion_backup

The actual swap of RPMs must partially be done manually. Remove the MySQL RPMs:

rpm -e `rpm -qa | grep MySQL`

The current state, we have data for 5.5 and no binaries/libraries installed. Install MariaDB:

da build set mariadb 5.5
da build set mysql_inst mariadb
da build mariadb

Recompile anything that uses MySQL/MariaDB, mainly just PHP:

da build php
da build roundcube

Enable backups for future updates, and enable mysqld monitoring again:

perl -pi -e 's/mysqld=OFF/mysqld=ON/' /usr/local/directadmin/data/admin/services.status
da build set mysql_backup yes

Check over everything to ensure it's running correctly. If not, going back to MySQL 5.5 would use roughly the same procedure.

Now that you're in the MariaDB family, you can upgrade to 10.0 or 10.1 from 5.5. I do not recommend going directly from MySQL 5.5 to MariaDB 10.x, even though it might work.

Rules for versions

Please see the MariaDB Documentation on upgrading from MySQL to MariaDBopen in new window to ensure the value you're converting to is supported, based on your current version's data.

Forcing `mysql_upgrade` after a version change

When MySQL/MariaDB is updated in CustomBuild, it should automatically call the mysql_upgrade command. Sometimes, you might be working with things manually, or just want try force another upgrade call.

To do this, run:

mysql_upgrade --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --force

where the --force option is optional, but sometimes needed if the script thinks it's already up to date, but you're not convinced it is.

Note, on Debian systems, the binary is at:

/usr/local/mysql/bin/mysql_upgrade --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --force

How to repair MySQL tables

If your system has crashed or MySQL tables have been corrupted somehow, there are few methods you can use to try and restore your database tables.

Note: MariaDB since version 5.1 uses myisam_recover_options that does auto-repair of crashed databases.

For Debian, please replace all instances of /var/lib, with /home.

E.g., /var/lib/mysql becomes /home/mysql .

Get DirectAdmin to do it for you.

DirectAdmin has repair commands built in, which make use of the SQL "repair table" options.

To use them (per-User basis), go to User Level -> MySQL Management -> Select the check-box for the DB to repair , and click "Repair"

OR

Use mysqlcheck tool to repair all databases on running mysql:

mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair -A -u root -p

OR

Use myisamchk and repair many databases at once on stopped mysql:

If you have many corrupted databases, and you want to repair them all in one shot, then login to ssh as root, and do the following:

a) Shut down mysqld:

Admin Level -> Services Monitor -> mysqld: stop

NOTE: Shutting down mysqld through DA is important!

If you don't, then the services.status won't be set, and the dataskq will end up starting it again, which is not likely what you want when repairing databases/tables.

b) Make a backup

cd /home
cp -Rp mysql mysql.backup

c) Repair the tables

/usr/bin/myisamchk --silent --force --fast --update-state \
         --key_buffer_size=64M --sort_buffer_size=64M \
         --read_buffer_size=1M --write_buffer_size=1M \
         */*.MYI

Note, on Debian, use this path instead:

/usr/local/mysql/bin/myisamchk

d) Start MySQL again:

Admin Level -> Services Monitor -> mysqld: start

Confirm all sites are working as intended and no further errors that would indicate corrupted/crashed databases/tables are being logged in MySQL's error log.

How to track MySQL Load

If you notice that the mysqld processes are using a lot of CPU load, there is a way to see which MySQL user is causing it.

Display the user/pass by running:

cat /usr/local/directadmin/conf/mysql.conf

Login to /phpMyAdmin through Apache (any website)
Click the "Processes" tab (near top right), or if you don't have Processes tab, click the "SQL" tab, and run the query

SHOW PROCESSLIST

You'll be shown a list of all current queries, who's running it, on which DB, and how long it's been going.

A query shouldn't take anymore than few seconds. Queries that take more than that are likely the source of the high load on the box.

The "Processes" tab has the option to kill a process, if needed. Using it may cause corrupted databases, so use sparingly.

If you find a specific User which is using more load than he should, newer MySQL versions have the ability to limit resources. As of DA 1.37.0, this feature is not in the interface, but if you login to /phpMyAdmin again (see #2 above), go to the database mysql then the table user and edit the user in question, and adjust the options:

max_queries
max_updates
max_connections
max_user_connections

as needed. See the MySQL documentation for the correct usage of these values. Incorrect usage will likely upset your client and break their website.

How to Optimize MySQL

IMPORTANT

Always make full backups of all of your databases before making any changes to your my.cnf.
Avoid changes to your InnoDB settings unless you are very familiar with making those changes. Many of the InnoDB settings must remain the same else data corruption will occur with your InnoDB tables. We do not recommend making any changes to your InnoDB settings, unless you do so before creating your databases.
Optimizing MySQL is not that easy, it may require more tryouts from you and adjustments based on queries type, amount of memory on a server etc. etc.

CentOS

There is a default my.cnf that comes with MySQL (4+5) that will make MySQL run a bit quicker if you have 2+ gig of ram

cp -f /usr/share/mysql/my-large.cnf /etc/my.cnf

There is also my-huge.cnf, or my-medium.cnf depending on your hardware setup. Check the contents of these my*.cnf files for the one that's right for you.

NOTE 1 the log-bin option is enabled by default. This will quickly use a lot of disk space. It's recommended to comment out the log-bin line from your /etc/my.cnf, if it exists.

NOTE 2 Take note of your old /etc/my.cnf file. If you have innodb_file_per_table=1, make sure the new my.cnf you install also has this setting. Similarly, if your old one does not have innodb_file_per_table=1 enabled, then your new my.cnf should also not have it enabled. If the new my.cnf has a different setting for innodb_file_per_table, then it may corrupt your data.

Be sure to make full backups of your .sql files before doing any changes to your my.cnf.

Debian

We don't currently have optimized my.cnf files for these OSs. The /etc/my.cnf will rely on the internal defaults in the mysqld binaries.

I have many mysql-bin files which are using up a lot of space

If your MySQL data directory has many files such as:

mysql-bin.000001
mysql-bin.000002
...

that means that the [mysqld] option:

log-bin=mysql-bin

is enabled in your /etc/my.cnf.

The solution to prevent those files from being created is to comment out the log-bin option from the my.cnf by adding at # character at the start of the line (left side).

The internal default is "OFF", hence removing it will disable it.

You can delete your mysql-bin.0* files after mysqld has been restarted.

How to transfer a database from one user to another

This guide will outline the method to transfer a database from one User account to another. Note, the database will be renamed during the process, changing the name to lead with the new User's username, so any scripts that use it would need to be updated.

For most cases, you'd want to include the db users with the DB, so rename_database_with_user.sh is the default here.

Assumptions:

Old Username: olduser New Username: newuser Database Name: olduser_dbname

Before any action, be sure to create a fully backup of your databases. If you don't access to DirectAdmin, you can create .sql backups with CustomBuild.

To move olduser_dbname to newuser_dbname including appropriate database user to new, run the following:

cd /usr/local/directadmin/scripts
./rename_database_with_user.sh olduser_dbname newuser_dbname

If you need just to move database without appropriate database user use the rename_database.sh script:

cd /usr/local/directadmin/scripts
./rename_database.sh olduser_dbname newuser_dbname

After the rename, be sure to go through all website configs that access this database, and update the settings accordingly.

Moving a database to a different user

If you want to move the databases from one user to another user, you can do so with the following command:

VERBOSE=1 DBUSER="da_admin" DBPASS="da_adminpass" USERNAME="username" NEWUSERNAME="newusername" /usr/local/bin/php /usr/local/directadmin/scripts/change_database_username.php

Where you can grab the da_adminpass from /usr/local/directadmin/conf/mysql.conf, and of course, replace "da_adminpass", "username", and "newuseraname" in the command above with the appropriate information as desired.

How to reinstall mysql

Before doing anything

We recommend you make full DA backups for easy restore, and CustomBuild mysql backups, just in case.

New Method

CustomBuild 2.0 rev 2914 has the ability to very easily start with a fresh MySQL/MariaDB install of any version.

It will check to see if /var/lib/mysql exists, and if not will do a fresh install of whatever is setup in the options.conf, AND will setup your root/da_admin accounts for you.

Wipe your old data. Of course this assumes you have nothing to lose, or you've made full .sql backups which you plan on restoring manually later on.

perl -pi -e 's/mysqld=ON/mysqld=OFF/' /usr/local/directadmin/data/admin/services.status
service mysqld stop
mv /var/lib/mysql /var/lib/mysql.old

Install the new desired version, say mariadb 10.4 or mysql 5.7, for example. Except you may want to use "mysql_backup no" since mysqld isn't running.
Rebuild the RoundCube user+db:

./build roundcube

Restore your .sql data, if you had any (avoid touching mysql.*, unless you know what you're doing. Create Users through DA if you're not confident)

How to limit MySQL usage for users

MySQL permits limits for individual database user accounts for the following:

Limit	Description
MAX_QUERIES_PER_HOUR	an integer representing the number of queries an account can issue per hour (mysql statements count against the query limit)
MAX_UPDATES_PER_HOUR	and integer representing the number of updates an account can issue per hour (mysql statements that result in modifications count against this limit)
MAX_CONNECTIONS_PER_HOUR	and integer representing the number of times an account can connect to the server per hour (‥account” in this context corresponds to a row in the mysql.user system table)
MAX_USER_CONNECTIONS	the number of simultaneous connections to the server by an account (an integer representing the maximum number of simultaneous connections by the account)

A database user account in this sense is one with a unique database username and a unique database hostname. So, fred on remote.mysqlbox.tld is separate account from fred on localhost.

All of the limits listed above can be configured globally or per database user account.

Global Limits

Simply edit the /etc/my.cnf with the desired limits and restart MySQL.

Let's say you wanted to limit all users to 200 max connnections. You would add the following in your /etc/mysql.cnf and restart mysql (service mysqld restart):

max_user_connections=200

IMPORTANT: If you do this, you'll likely want to manually adjust the limits the following users so that they may exceed this limit:

da_admin
root
da_roundcube
da_atmail

You'll need to set these to a sufficiently high, non-zero value to bypass the global limit.

Per-User Limits

Create a new user or edit an existing user with the limits using SQL statements. You can use Create statements for new users and Alter statements for existing users. Since DirectAdmin will likely be creating your users for you, you will need only the Alter syntax.

Note that you may have to log out of the mysql> CLI session and back in to see per-User limit changes applied.

Syntax:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf
ALTER USER 'fred'@'localhost' WITH MAX_QUERIES_PER_HOUR 200;
quit

To remove the limit for this user, run the same but set to 0 instead of 200.

Using Both Global and Per-User Limits

If a user has a non-zero limit, that limit is used. Otherwise, the global limit is used.

If the user limit is 0, and the global limit is non-zero, then the global limit is used. If both the user limit and the global limit are 0, then the limit is unlimited.

A user limit could exceed the global limit, thus allowing the useful functionality of setting a maximum value globally, and only allowing certain users to bypass this.

Automating per-User MySQL Limits

Below is a script for limiting MySQL resources for every new MySQL user. It limits the number of queries, updates, and logins a MySQL user can perform. The script originates from the forum post here:

https://forum.directadmin.com/threads/how-to-limit-mysql-usage-for-users.34588/open in new window

Copy and paste the code below to both of the following files:

/usr/local/directadmin/scripts/custom/database_user_create_post.sh
/usr/local/directadmin/scripts/custom/database_create_post.sh

Code:

#!/bin/sh
#This script sets the number of queries, updates, and logins a new MySQL user can perform

#Limits (setting these options to 0 removes the limit)
MAX_QUERIES_PER_HOUR=30000
MAX_UPDATES_PER_HOUR=20000
MAX_CONNECTIONS_PER_HOUR=5000
MAX_USER_CONNECTIONS=100

#We get DirectAdmin MySQL root user and password here
DA_MYSQL=/usr/local/directadmin/conf/mysql.conf
MYSQLUSER=`grep "^user=" ${DA_MYSQL} | cut -d= -f2`
MYSQLPASSWORD=`grep "^passwd=" ${DA_MYSQL} | cut -d= -f2`

mysql -e "GRANT ALL ON ${database}.* TO ${user}@'localhost' IDENTIFIED BY '${passwd}' WITH MAX_QUERIES_PER_HOUR ${MAX_QUERIES_PER_HOUR} MAX_UPDATES_PER_HOUR ${MAX_UPDATES_PER_HOUR} MAX_CONNECTIONS_PER_HOUR ${MAX_CONNECTIONS_PER_HOUR} MAX_USER_CONNECTIONS ${MAX_USER_CONNECTIONS};" --user=${MYSQLUSER} --password=${MYSQLPASSWORD}
exit 0;

Fix the permissions:

chmod 755 /usr/local/directadmin/scripts/custom/database_user_create_post.sh
chmod 755 /usr/local/directadmin/scripts/custom/database_create_post.sh

Note that the script above automates the application of limits for new database user creations. If you want to set a limit for all current MySQL users, use the following script:

Code:

#!/bin/sh

#Limits (setting these options to 0 removes the limit)
MAX_QUERIES_PER_HOUR=30000
MAX_UPDATES_PER_HOUR=20000
MAX_CONNECTIONS_PER_HOUR=5000
MAX_USER_CONNECTIONS=100

#We get DirectAdmin MySQL root user and password here
DA_MYSQL=/usr/local/directadmin/conf/mysql.conf
MYSQLUSER=`grep "^user=" ${DA_MYSQL} | cut -d= -f2`
MYSQLPASSWORD=`grep "^passwd=" ${DA_MYSQL} | cut -d= -f2`

mysql -e "use mysql; UPDATE mysql.user SET max_questions=${MAX_QUERIES_PER_HOUR}, max_updates=${MAX_UPDATES_PER_HOUR}, max_connections=${MAX_CONNECTIONS_PER_HOUR}, max_user_connections=${MAX_USER_CONNECTIONS} WHERE user!='da_admin' AND user!='root' AND user!='da_roundcube' AND user!='da_atmail'; FLUSH PRIVILEGES;" --user=${MYSQLUSER} --password=${MYSQLPASSWORD}

echo "Limits have been set."
exit 0;

That's it! 😃

# General MariaDB and MySQL

# Where is my my.cnf?

# local-infile=0 in your /etc/my.cnf

# How to upgrade MySQL / MariaDB with CustomBuild 2.0

# Connect to your MySQL database from a remote connection

# Setting up DA to use a remote MySQL server

# Converting from MySQL 5.5 to MariaDB 5.5

# wait for the backup to complete, then rename it to a unique folder so it's not overwritten:

# Forcing mysql_upgrade after a version change

# How to repair MySQL tables

# OR

# OR

# How to track MySQL Load

# How to Optimize MySQL

# CentOS

# Debian

# I have many mysql-bin files which are using up a lot of space

# How to transfer a database from one user to another

# Assumptions:

# Moving a database to a different user

# How to reinstall mysql

# Before doing anything

# New Method

# How to limit MySQL usage for users

# Global Limits

# Per-User Limits

# Using Both Global and Per-User Limits

# Automating per-User MySQL Limits