Database Servers

To install MySQL on different Linux distributions, use the following commands:

• Redhat/AlmaLinux: pgsqlCopysudo dnf install mysql-server sudo systemctl enable mysqld sudo systemctl start mysqld
• Debian/Ubuntu: pgsqlCopysudo apt update sudo apt install mysql-server sudo systemctl enable mysql sudo systemctl start mysql
• CloudLinux: pgsqlCopysudo yum install mysql-server sudo systemctl enable mysqld sudo systemctl start mysqld

To secure the MySQL installation:

• Run the MySQL secure installation script: nginxCopysudo mysql_secure_installation
• The script will prompt you to configure the root password, remove insecure default settings, and secure your installation by removing anonymous users and test databases.

To install PostgreSQL on different Linux distributions, use the following commands:

• Redhat/AlmaLinux: pgsqlCopysudo dnf install postgresql-server postgresql-contrib sudo postgresql-setup –initdb sudo systemctl enable postgresql sudo systemctl start postgresql
• Debian/Ubuntu: pgsqlCopysudo apt update sudo apt install postgresql postgresql-contrib sudo systemctl enable postgresql sudo systemctl start postgresql
• CloudLinux: pgsqlCopysudo yum install postgresql-server postgresql-contrib sudo postgresql-setup initdb sudo systemctl enable postgresql sudo systemctl start postgresqltest databases.

To create a new database and user in MySQL:

• Log in to MySQL: cssCopymysql -u root -p
• Create a new database: pgsqlCopyCREATE DATABASE mydatabase;
• Create a new user and grant permissions: pgsqlCopyCREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypassword’; GRANT ALL PRIVILEGES ON mydatabase.* TO ‘myuser’@’localhost’; FLUSH PRIVILEGES;

To create a new database and user in PostgreSQL:

• Log in to PostgreSQL as the postgres user: nginxCopysudo -u postgres psql
• Create a new database: pgsqlCopyCREATE DATABASE mydatabase;
• Create a new user and grant permissions: pgsqlCopyCREATE USER myuser WITH ENCRYPTED PASSWORD ‘mypassword’; GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

To back up and restore a MySQL database:

• Back up a database: pgsqlCopymysqldump -u root -p mydatabase > /path/to/backup/mydatabase.sql
• Restore a database: pgsqlCopymysql -u root -p mydatabase < /path/to/backup/mydatabase.sql

To back up and restore a PostgreSQL database:

• Back up a database: pgsqlCopypg_dump mydatabase > /path/to/backup/mydatabase.sql
• Restore a database: pgsqlCopypsql mydatabase < /path/to/backup/mydatabase.sql

To configure MySQL replication:

• On the master server, configure the server ID and enable binary logging: Edit /etc/my.cnf: iniCopy[mysqld] server-id = 1 log-bin = mysql-bin On the slave server, configure the server ID and point to the master server: Edit /etc/my.cnf: iniCopy[mysqld] server-id = 2 relay-log = mysql-relay-bin Create a replication user on the master server: pgsqlCopyCREATE USER ‘replica’@’%’ IDENTIFIED BY ‘replicapassword’; GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’%’; FLUSH PRIVILEGES; On the slave server, start replication: nginxCopyCHANGE MASTER TO MASTER_HOST=’master_ip’, MASTER_USER=’replica’, MASTER_PASSWORD=’replicapassword’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 154; START SLAVE;

To monitor the performance of a database server:

• MySQL: Use mysqladmin to check server status: cssCopymysqladmin -u root -p status Use SHOW STATUS and SHOW VARIABLES within the MySQL command line to monitor performance metrics.
• PostgreSQL: Use the pg_stat_activity view to check active queries: pgsqlCopySELECT * FROM pg_stat_activity; Use top or htop for system-level monitoring of CPU and memory usage.