Archive

Archive for the ‘MySql’ Category

How to set max_open_files in MariaDB / MySQL in CentOS 7

July 25th, 2022 Comments off

Set the system wide open file limit:

vi /etc/security/limits.conf

Change/Add the following:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

Now do this for /etc/sysctl

vi /etc/sysctl

Add the following

fs.file-max = 1024000

Set the changes

sysctl -w fs.file-max=1024000
sysctl -p
# check changes
cat /proc/sys/fs/file-max

Set the mysqld.service limit (as settings here will override *.cnf ones)

Set both /etc/systemd/system.conf and /etc/systemd/user.conf

vi /etc/systemd/system.conf
vi /etc/systemd/user.conf

Add the following under [Manager] for both:

DefaultLimitNOFILE=1024000

ALSO, you may need to look in /etc/systemd/system to see if anything is overriding stuff.

/etc/systemd/system
grep -Rl LimitNOFILE

Then change all instances of “LimitNOFILE” with:

systemctl edit [name of service].service

Or do this via “vi”

LimitNOFILE=infinity
LimitMEMLOCK=infinity

You may even need to use the following:

LimitAS=infinity
LimitRSS=infinity
LimitCORE=infinity
LimitNOFILE=infinity

START METHOD1

Find out which .conf files are being used:

systemctl status mysqld
# You'll get something like the following
Drop-In: /etc/systemd/system/mariadb.service.d
           ??override.conf

So now that we see Drop-In: /etc/systemd/system/mariadb.service.d, we’ll do the following:

cd /etc/systemd/system/mariadb.service.d
# If you're using regular mysql, then the above path will likely be different
vi /etc/systemd/system/mariadb.service.d/override.conf

Add the following:

[Service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity

If that doesn’t work, then “infinity” variable was set to mean a specific number, like “65536”… If that’s the case, set the same number as you did in “/etc/my.cnf.d/server.cnf”
or set a really high number like “2048000”

[Service]
LimitNOFILE=2048000
LimitMEMLOCK=2048000

END METHOD 1

START METHOD 2

Find the location of all potential *.service files

cd /
find -iname maria*.service
# or
find -iname mysql*.service

Then edit each one, as in example below:

vi /usr/local/directadmin/custombuild/configure/systemd/mysqld57.service
vi /usr/local/directadmin/custombuild/configure/systemd/mysql.service
vi /usr/local/directadmin/custombuild/configure/systemd/mysqld.service
vi /etc/systemd/system/mysqld.service

vi /etc/systemd/system/mariadb.service
vi /usr/share/mysql/systemd/mariadb.service
vi /usr/local/directadmin/custombuild/configure/systemd/mariadb.servicevi 

Change/Add the following, under “[Service]”:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

If that doesn’t work, then “infinity” variable was set to mean a specific number, like “65536”… If that’s the case, set the same number as you did in “/etc/my.cnf.d/server.cnf”
or set a really high number like “2048000”

LimitNOFILE=2048000
LimitMEMLOCK=2048000

END METHOD 2

Set the *.cnf settings:

vi /etc/my.cnf.d/server.cnf
# or where ever your .cnf may be

Change/Add the following:

open_files_limit               = 1024000

Now reload/restart what’s necessary:

systemctl daemon-reload
systemctl restart mysqld; systemctl status mysqld

How to ‘safely’ delete Mysql relay-bin log file??

April 21st, 2022 Comments off

Disk is getting full. When using du, I could see my MySQL log folder is taking up all the disk. These files are not mysql-bin files, instead, these are mysql-relay-bin files. How can I truncate or purge these files?

Mysql creates mysql-bin files which are called binary files for MySQL on the master side. It then streams the binary files to the replicas to reflect the changes. Mysql doesn’t really store the binaries on the replica side, hence it is safe to delete the relay-bin files. Although, there are other safe strategies to accomplish this or never fall into a situation of ‘out of space’.

Firstly, we need to understand, expire_log_days MySQL attribute doesn’t work for the replica. The reason is simple, it doesn’t store the binary files. Hence, using this is worthless for replicas. You can’t use ‘PURGE’ SQL command either for Replica log purging. Replica has a special attribute ‘relay_log_purge’ which purges the relay-bin logs periodically. You can also set replica to follow a specific size of the log file by using ‘relay_log_space_limit’ attribute.

Now at a very certain time, if you want to free up space, other than gross and brutal deleting relay-bin log files, what you can do is the following:

# start your mysql console session
mysql
# stop the slave
stop slave;
# reset the slave
reset slave;
# now start the slave again
start slave;

Now, once the slave is reset, it will start streaming the bin log from the “pos” it has in its queue to populate the pending jobs.

Once the sync is done, you may check the replica status using

show slave status \G;

Make sure the following two are set to yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If not, you should look at the ‘Last_SQL_Error’ or ‘Last_IO_Error’ section to find out why these are not pushing.

Categories: MySql Tags: , , ,

How to replace MySQL with MariaDB in cpanel

September 30th, 2014 Comments off

Backup existing MySQL data

Make sure to save all existing data just in case there are any issues.

cp -Rf /var/lib/mysql /var/lib/mysql-old
mv /etc/my.cnf /etc/my.cnf-old

Disable the targets so cPanel no longer handles MySQL updates

The following will mark the versions of MySQL we distribute as uninstalled so they are no longer maintained by cPanel/WHM

/scripts/update_local_rpm_versions –edit target_settings.MySQL50 uninstalled
/scripts/update_local_rpm_versions –edit target_settings.MySQL51 uninstalled
/scripts/update_local_rpm_versions –edit target_settings.MySQL55 uninstalled

Remove existing MySQL RPM’s so theres a clean slate for MariaDB

The below command will uninstall the MySQL RPM’s!

/scripts/check_cpanel_rpms –fix –targets=MySQL50,MySQL51,MySQL55

Create a yum repository for MariaDB

vi /etc/yum.repos.d/MariaDB.repo

place the following inside of it depending on the DISTRO (https://downloads.mariadb.org/mariadb/repositories/):

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5.29/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Remove php from the /etc/yum.conf file then run the following commands

yum install MariaDB-server MariaDB-client MariaDB-devel
/etc/init.d/mysql start
mysql_upgrade
/etc/init.d/mysql restart

Add php back to the /etc/yum.conf file to ensure future php updates don’t get clobbered

Rebuild easyapache/php to ensure modules are intact/working

/scripts/easyapache –build

 

mysql is disabled by sys admin while restarting mysql service

December 15th, 2012 Comments off

Sometimes it may happen that while restarting mysql service on server you are getting below error.

mysql is disabled by sys admin

Try running below script to bring mysql up again

/scripts/mysqlup

If it’s Up then ok, but sometime it will give below error

Updates for mysql /etc/mysql disabled

If it is not working then go to /etc folder

cd /etc

Run the below command there

root@testserver [/etc]#ls -ld mysql*

It will give below output

-rw-r–r– 1 root root 0 Aug 16 21:19 mysqldisable

Move that file using below command

root@testserver[/etc]# mv mysqldisable mysqldisable-bak

Then restart mysql will fix your issue.

root@testserver[/etc]# /scripts/mysqlup

 

Categories: CPanel / WHM, MySql Tags:

IP remove from Brute Force Attack database by SSH

December 15th, 2012 Comments off

Sometimes, because of the Brute Force Attack we are unable to login to WHM if we are using correct details also. Then at that time try using below method.

 

Login to server via SSH.

Connect with mysql database using mysql command. It will show below output.

root@testserver [/var/log]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 172123
Server version: 5.1.65-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Then type show databases commands in mysql it will show all databases.

 

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| cphulkd |
| eximstats |
| horde |
| leechprotect |
| logaholicDB_test |
| modsec |
| mysql |
| roundcube |
+——————–+
9 rows in set (0.00 sec)

Then go to cphulkd database using below command.

mysql> use cphulkd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Now, it’s time to show tables.

mysql> show tables;
+——————-+
| Tables_in_cphulkd |
+——————-+
| auths |
| blacklist |
| brutes |
| good_logins |
| logins |
| report |
| whitelist |
+——————-+
7 rows in set (0.00 sec)

You can take backup of tables using below command

mysql>BACKUP TABLE `brutes` TO ‘/path/to/backup/directory’;

Run below command to check your IP is blocked in CPHulk Brute Froce database. Replace xxx.xxx.xxx.xxx with your IP

mysql> SELECT * FROM `brutes` WHERE `IP`=’xxx.xxx.xxx.xxx’;

Run below command to remove IP from database

mysql> DELETE FROM `brutes` WHERE `IP`=’xxx.xxx.xxx.xxx’;

Also, check IP in login table also by running the below command

mysql> SELECT * FROM ‘logins’ WHERE ‘IP’ =’xxx.xxx.xxx.xxx’;

Remove the IP from login table using below command.

mysql> DELETE FROM ‘logins’ WHERE ‘IP’ =’xxx.xxx.xxx.xxx’;

OR

You can use below method to clear brutes and login tables. Please note it will remove all the IP’s from database.

Now empty some tables by giving the below commands.

mysql> DELETE FROM brutes;
Query OK, 60 rows affected (0.00 sec)

mysql> DELETE FROM logins;
Query OK, 34 rows affected (0.00 sec)

 

 

9 rows in set (0.00 sec)

 

Categories: CPanel / WHM, MySql Tags:
%d bloggers like this: