Modern versions of MySQL is memory intensive; thus, it is a pre-requisite that an adequate amount of RAM is present on a MySQL server prior to its installation. However, slimmer instances of VPS or AWS/VMWare may not have sufficient memory to run MySQL. Hence, swap files must be created to augment the modest RAM allocation in these types of machines. Below is a procedure to add swap memory on a CentOS image:
# Check memory & swap file
free -m
# Check available disk space
df -h
# Allocate swapfile, set appropriate permissions, create swapfile
sudo dd if=/dev/zero of=/swapfile count=4096 bs=1MiB #allocate
chmod 600 /swapfile #secure the directory
mkswap /swapfile #make swapfile in the /swapfile directory
swapon /swapfile #configure system to use /swapfile
# Check swapfile settings
swapon -s
# Make permanent
vim /etc/fstab
# add this line
/swapfile none swap defaults 0 0
Run MySQL on Localhost:
# Reinstall MySQL
yum remove mysql mysql-server
mv /var/lib/mysql /var/lib/mysql_old_backup
# or just delete: rm -rf /var/lib/mysql
yum install mysql mysql-server
# Set default password format – this must be done prior to creating any new users!
vim /etc/my.cnf
# add this line
default_authentication_plugin=mysql_native_password
# Set mysql to autostart
systemctl enable mysqld
systemctl start mysqld
# Obtain the initial password
grep ‘temporary password’ /var/log/mysqld.log
# Secure mysql
mysql_secure_installation
# set password
# Send command from bash terminal
mysql -uroot -ppassword -e “COMMAND1;COMMAND2;”
# Example
mysql -uroot -ppassword -e “ALTER USER kimconnect IDENTIFIED BY ‘password\!’;”
# Create database, user & grant access
mysql -uroot -ppassword -e “CREATE DATABASE kimconnect;CREATE USER kimconnect@’%’ IDENTIFIED BY ‘password’;GRANT ALL PRIVILEGES ON kimconnect.* TO ‘kimconnect’@’%’;flush privileges;”
# Individual commands:
# Set password
SET PASSWORD FOR ‘kimconnect’@’%’ = PASSWORD(‘password’);
# Update password
ALTER USER kimconnect IDENTIFIED BY ‘password’;
# Grant access
mysql -uroot -ppassword -e “GRANT USAGE ON kimconnect.* TO ‘kimconnect’@’%’ IDENTIFIED BY ‘password’;”
# Remove user
DROP USER kimconnect;
mysql -uroot -ppassword -e “DROP USER kimconnect;”
# Reload accesses
flush privileges;
# Grant remote accesses
mysql -uroot -ppassword-e “GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;FLUSH PRIVILEGES;”
mysql -uroot -ppassword -e “GRANT ALL PRIVILEGES ON kimconnect.* TO ‘kimconnect’@’%’ WITH GRANT OPTION;FLUSH PRIVILEGES;”
# Check connection
mysql -ukimconnect-ppassword -e “show databases;” -h 172.17.0.x
Troubleshooting
# connect to mysqld
mysql -uroot -p
# input password
# ERROR 2002 (HY000): ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysqld/mysqld.sock’ (111)’
# This could be caused by an incorrect password. If necessary, restart mysqld
systemctl restart mysqld
# Check password policy
SHOW VARIABLES LIKE ‘validate_password%’;
# Change any global variable:
# SET GLOBAL validate_password_length = 12;
# SET GLOBAL validate_password_number_count = 3;
# SET GLOBAL validate_password_mixed_case_count = 1;
# SET GLOBAL validate_password_special_char_count = 1;
# SET GLOBAL validate_password_policy = HIGH;
# Look at all the sockets
find / -type s
# Located socket at: /run/mysqld/mysqlx.sock
pkill mysqld
Out of memory error:
InnoDB: Fatal error: cannot allocate memory for the buffer pool
# Resolution: increase RAM or add a swap file onto the host
ERROR 2059 (HY000): Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so
# Resolution:
vim /etc/my.cnf
# add this line
default_authentication_plugin=mysql_native_password