Top Nav

Archive | Databases

MySQL InnoDB File Per Table

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

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 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:

 

 

 

 

 

2

Install Percona Server 5.5 On CentOS 6.5

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

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