Top Nav

Archive | Databases

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

Percona XtraDB Upgrade With Yum Shell

Ran into dependency problems today trying to upgrade a Percona XtraDB server cluster. Yum shell to the rescue:

This way I can run the remove and installs as part of a single transaction.

0

Scripting CHECK / REPAIR TABLES

Here’s a one-liner that will check all tables in a database:

http://codeinthehole.com/writing/checking-all-mysql-tables/

And here’s an article that shows the use of mysqlcheck:

http://www.thegeekstuff.com/2011/12/mysqlcheck/

mysqlcheck, unlike myisamchk, can be run when the mysql daemon is running

This command will check, repair and optimize all tables in all databases:

 

0