Top Nav

Navigation

Archive | Databases

MySQL InnoDB File Per Table

By on May 22, 2016 in MySQL

By default MySQL 5.5 and prior store all InnoDB tables in a single table space or file. Improved performance and managability can be accheived by using the innodb_file_per_table option to cause MySQL to use a separate file for each table. This option is enabled by default on MySQL 5.6 and later.

To implement:
1. Add option to my.cnf:

2. Change table to use single file:

If you want to covert all databases then dump the entire database and import back into the server.

 

0

Mysqldump TRIGGERS Only

By on September 17, 2015 in MySQL

Here’s the command to dump the triggers and stored procedures for a database:

 

0

MariaDB on CentOS 7 – “Error in accept: Too many open files”

By on August 28, 2015 in CentOS, MariaDB, Nginx

By default is seems the soft and hard open files limits on MariaDB in CentOS 7 are 1024 and 4096 respectfully. You can see these limits by first getting the process ID:

And then looking at the limits in the proc filesystem:

You’ll see something like this:

Notice the numbers for “Max open files”.

If you run into problems with MariaDB failing and you see errors like this in the log:

Then you need to increase the open files limits by editing:

and adding this line:

to the “[Service]” section. Then reload the systemctl daemon:

and restart the MariaDB service:

Now the limit will be increased.  For example:

UPDATE: We’ve seen similar problems with nginx. The solution is similar … increase the limits for the nginx service.

UPDATE: As noted by Bastiaan Welmers in the comments, it better to copy the service control file then to edit:

UPDATE: 

As describe here:

https://docs.fedoraproject.org/en-US/quick-docs/systemd-understanding-and-administering/#_modifying_existing_systemd_services

Create an override file with:

or:

Put the modified settings in the override file:

Reload systemd config:

And restart mariadb:

UPDATE:

On server with Plesk, view the current open files limit with:

UPDATE:

On Ubuntu 22 and possibly other environments some additional steps were necessary to the limit.

The kernel contains a compiled default upper limit for open files. The limit can be shown with:

Ubuntu 22 sets the limit to 1048576. The limit can be increased on the fly with:

or to change permanently:

For Apache, the upper limit may be set to 8192 with this line:

in:

The line may be commented out in which case the 8192 default limit is applied. To change, uncomment and set desired value.

4

Install Percona Server 5.5 On CentOS 6.5

By on January 24, 2015 in Percona

Today we’re going to install Percona Server 5.5 on a CentOS 6.5 server. Our server is hosted on Amazon AWS but the procedure should be the same for any host.

Percona Server is a drop-in replacement for MySQL that features enhanced performance. You can learn more about Percona here:

http://www.percona.com/

We’re going to do our install using the Percona Yum repository. The documentation is here:

http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html

Lets get started by adding the repository. Run this command as root:

and you’ll get output similar to this:

Now if you run this command:

You’ll see a list of available packages like:

Since our goal is to install Percona 5.5, we’ll do the following:

The install proceeds like this:

As you can see a number of supporting packages were also installed.

Next let’s configure Percona to start on boot:

And run it for the first time:

which should give some output like:

Notice that the service name for Percona is “mysql” instead of “mysqld” which is used by MySQL.

At this point the Percona Server 5.5 install has been completed.

 

 

1

Revoke MySQL DROP Privilege On Plesk Database User

By on November 8, 2014 in MySQL, Plesk

Had a request from client to add database user on a Plesk server but to remove the DROP DATABASE privilege so that they could not accidentally delete their database. By default, database users created through Plesk do have the DROP DATABASE privilege. Plesk does not currently allow fine-grained control over database privileges so the only way to implement this change is to modify the privileges directly from a MySQL command line. After some testing we found these command will have the desired effect:

Of course replace “testdb” and “testdbuser” with your actual database and username.

0