MySQL Master/Slave Replication Configuration Commands

Step 1 — Installing MySQL

Update the system packages, Then install the mysql-server package:

1) sudo apt-get update

2) sudo apt-get upgrade

3) sudo apt-get dist-upgrade

4) sudo reboot

sudo apt install mysql-server

Step 2 - Start MySQL & Connect- By default MySQL connected using Native root without password, we will configure it to use password

sudo systemctl start mysql.service

systemctl status mysql.service

mysql -- Connected without password using root user

Configuring the Server to Start at Startup¶

You can manage the server with systemd. If you have installed the server from a generic binary distribution on an operating system that uses systemd, you can manually configure systemd support.

The following commands start, check the status, and stop the server:

$ sudo systemctl start mysql

$ sudo systemctl status mysql

$ sudo systemctl stop mysql

Enabling the server to start at startup, run the following:

$ sudo systemctl enable mysql

Step 3 — Configuring MySQL WITHOUT auth_socket;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'fD@8RpUy';

--Reconnect and check

mysql -uroot -p -A

--fD@8RpUy

Step 4 - Create DBA Login,

CREATE USER 'anurag_test'@'%' IDENTIFIED BY 'Y0urP@$$123';

CREATE USER 'ptdbaanurag'@'%' IDENTIFIED BY 'Y0urP@$$123';

GRANT ALL PRIVILEGES ON *.* TO 'anurag_test'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'ptdbaanurag'@'%' WITH GRANT OPTION;

Step 4 - Configuring Auditing

Copy audit_log file to plugin directory /usr/lib/mysql/plugin

- Connect with MySQL and Check path of plugin dir and install plugin

root@anuragdbm01:/usr/lib/mysql/plugin# mysql -uroot -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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>

mysql> SHOW variables LIKE 'audit%';

Empty set (0.01 sec)

mysql> SHOW variables LIKE 'plugin%';

+---------------+------------------------+

| Variable_name | Value |

+---------------+------------------------+

| plugin_dir | /usr/lib/mysql/plugin/ |

+---------------+------------------------+

1 row in set (0.00 sec)

mysql>

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G

*************************** 1. row ***************************

PLUGIN_NAME: audit_log

PLUGIN_VERSION: 0.2

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: AUDIT

PLUGIN_TYPE_VERSION: 4.1

PLUGIN_LIBRARY: audit_log.so

PLUGIN_LIBRARY_VERSION: 1.11

PLUGIN_AUTHOR: Percona LLC and/or its affiliates.

PLUGIN_DESCRIPTION: Audit log

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

1 row in set (0.00 sec)

-- Check data directory path

mysql> show global variables like 'data%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.01 sec)

mysql> exit

root@anuragdbm01:/usr/lib/mysql/plugin#

root@anuragdbm01:/usr/lib/mysql/plugin# cd /var/lib/mysql/

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# ls -lrth

total 90M

-rw-r----- 1 mysql mysql 56 Aug 16 06:04 auto.cnf

-rw-r----- 1 mysql mysql 8.2M Aug 16 06:04 '#ib_16384_1.dblwr'

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 performance_schema

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 ca.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 ca-key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 server-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 server-cert.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 client-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 client-cert.pem

-rw-r--r-- 1 mysql mysql 452 Aug 16 06:04 public_key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 private_key.pem

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 mysql

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 sys

-rw-r--r-- 1 root root 0 Aug 16 06:05 debian-5.7.flag

-rw-r----- 1 mysql mysql 180 Aug 16 06:05 binlog.000001

-rw-r----- 1 mysql mysql 404 Aug 16 06:05 binlog.000002

-rw-r----- 1 mysql mysql 3.4K Aug 16 06:05 ib_buffer_pool

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:05 '#innodb_temp'

-rw-r----- 1 mysql mysql 48 Aug 16 06:05 binlog.index

-rw-r----- 1 mysql mysql 5 Aug 16 06:05 anuragdbm01.pid

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:05 '#innodb_redo'

-rw-r----- 1 mysql mysql 12M Aug 16 06:05 ibtmp1

-rw-r----- 1 mysql mysql 2.4K Aug 16 06:16 binlog.000003

-rw-r----- 1 mysql mysql 16M Aug 16 06:18 undo_001

-rw-r----- 1 mysql mysql 16M Aug 16 06:30 undo_002

-rw-r----- 1 mysql mysql 25M Aug 16 06:30 mysql.ibd

-rw-r----- 1 mysql mysql 12M Aug 16 06:30 ibdata1

-rw-r----- 1 mysql mysql 192K Aug 16 06:30 '#ib_16384_0.dblwr'

-rw-r----- 1 mysql mysql 1.4K Aug 16 06:31 audit.log

root@anuragdbm01:/var/lib/mysql# mkdir audit

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# chown -R mysql:mysql /var/lib/mysql

root@anuragdbm01:/var/lib/mysql# mkdir binlog

root@anuragdbm01:/var/lib/mysql# cd /etc/mysql/mysql.conf.d

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# ls -lrth

total 8.0K

-rw-r--r-- 1 root root 2.2K Jun 14 2023 mysqld.cnf

-rw-r--r-- 1 root root 132 Jun 14 2023 mysql.cnf

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# mv mysqld.cnf mysqld.cnf_16Aug2024

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# ls -lrth

total 8.0K

-rw-r--r-- 1 root root 2.2K Jun 14 2023 mysqld.cnf_16Aug2024

-rw-r--r-- 1 root root 132 Jun 14 2023 mysql.cnf

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# vi mysqld.cnf

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# systemctl restart mysql.service

Job for mysql.service failed because the control process exited with error code.

See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# systemctl status mysql

× mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

Active: failed (Result: exit-code) since Fri 2024-08-16 06:41:30 UTC; 14s ago

Process: 3857 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUC>

Process: 3865 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)

Main PID: 3865 (code=exited, status=1/FAILURE)

Status: "Server shutdown complete"

Error: 13 (Permission denied)

CPU: 255ms

Aug 16 06:41:30 anuragdbm01 systemd[1]: mysql.service: Scheduled restart job, restart counter i>

Aug 16 06:41:30 anuragdbm01 systemd[1]: Stopped MySQL Community Server.

Aug 16 06:41:30 anuragdbm01 systemd[1]: mysql.service: Start request repeated too quickly.

Aug 16 06:41:30 anuragdbm01 systemd[1]: mysql.service: Failed with result 'exit-code'.

Aug 16 06:41:30 anuragdbm01 systemd[1]: Failed to start MySQL Community Server.

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# chown -R mysql:mysql /var/lib/mysql

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# systemctl restart mysql.service

root@anuragdbm01:/etc/mysql/mysql.conf.d#

root@anuragdbm01:/etc/mysql/mysql.conf.d# systemctl status mysql.service

● mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

Active: active (running) since Fri 2024-08-16 06:43:51 UTC; 9s ago

Process: 3919 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Main PID: 3927 (mysqld)

Status: "Server is operational"

Tasks: 42 (limit: 18588)

Memory: 1.5G

CPU: 1.875s

CGroup: /system.slice/mysql.service

└─3927 /usr/sbin/mysqld

Aug 16 06:43:50 anuragdbm01 systemd[1]: Starting MySQL Community Server...

Aug 16 06:43:51 anuragdbm01 systemd[1]: Started MySQL Community Server.

root@anuragdbm01:/etc/mysql/mysql.conf.d#

Connect to MySQL & Check Variables output to Check its Properly Configured:

root@anuragdbm01:/etc/mysql/mysql.conf.d# mysql -uroot -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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>

mysql> SHOW variables LIKE 'audit%';

+-----------------------------+----------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -------+

| Variable_name | Value |

| audit_log_buffer_size | 1048576 |

| audit_log_exclude_accounts | root@localhost |

| audit_log_exclude_commands | |

| audit_log_exclude_databases | information_schema,mysql,performance_schema,sys |

| audit_log_file | /var/lib/mysql/audit/audit.log |

| audit_log_flush | OFF |

| audit_log_format | OLD |

| audit_log_handler | FILE |

| audit_log_include_accounts | |

| audit_log_include_commands | alter_db,alter_instance,alter_resource_group,alter_server,alter_table,alter_tablespace,a lter_user,alter_user_default_role,analyze,change_db,change_repl_filter,change_replication_source,create_db,create_resour ce_group,create_role,create_server,create_user,delete,drop_db,drop_event,drop_function,drop_index,drop_procedure,drop_re source_group,drop_role,drop_server,drop_spatial_reference_system,drop_table,drop_trigger,drop_user,drop_view,grant,grant _roles |

| audit_log_include_databases | |

| audit_log_policy | ALL |

| audit_log_rotate_on_size | 1073741824 |

| audit_log_rotations | 10 |

| audit_log_strategy | ASYNCHRONOUS |

| audit_log_syslog_facility | LOG_USER |

| audit_log_syslog_ident | percona-audit |

| audit_log_syslog_priority | LOG_INFO |

18 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G

*************************** 1. row ***************************

PLUGIN_NAME: audit_log

PLUGIN_VERSION: 0.2

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: AUDIT

PLUGIN_TYPE_VERSION: 4.1

PLUGIN_LIBRARY: audit_log.so

PLUGIN_LIBRARY_VERSION: 1.11

PLUGIN_AUTHOR: Percona LLC and/or its affiliates.

PLUGIN_DESCRIPTION: Audit log

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

1 row in set (0.00 sec)

mysql> SHOW variables LIKE 'audit%';

| Variable_name | Value |

| audit_log_buffer_size | 1048576 |

| audit_log_exclude_accounts | root@localhost |

| audit_log_exclude_commands | |

| audit_log_exclude_databases | information_schema,mysql,performance_schema,sys |

| audit_log_file | /var/lib/mysql/audit/audit.log |

| audit_log_flush | OFF |

| audit_log_format | OLD |

| audit_log_handler | FILE |

| audit_log_include_accounts | |

| audit_log_include_databases | |

| audit_log_policy | ALL |

| audit_log_rotate_on_size | 1073741824 |

| audit_log_rotations | 10 |

| audit_log_strategy | ASYNCHRONOUS |

| audit_log_syslog_facility | LOG_USER |

| audit_log_syslog_ident | percona-audit |

| audit_log_syslog_priority | LOG_INFO |

18 rows in set (0.00 sec)

mysql> show variables like 'binlog%'

-> ;

+------------------------------------------------+--------------+

| Variable_name | Value |

+------------------------------------------------+--------------+

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_encryption | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_expire_logs_auto_purge | ON |

| binlog_expire_logs_seconds | 0 |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_rotate_encryption_master_key_at_startup | OFF |

| binlog_row_event_max_size | 8192 |

| binlog_row_image | FULL |

| binlog_row_metadata | MINIMAL |

| binlog_row_value_options | |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlog_transaction_compression | OFF |

| binlog_transaction_compression_level_zstd | 3 |

| binlog_transaction_dependency_history_size | 25000 |

| binlog_transaction_dependency_tracking | COMMIT_ORDER |

+------------------------------------------------+--------------+

24 rows in set (0.00 sec)

mysql>

mysql> show variables like 'bin%'

-> ;

+------------------------------------------------+--------------+

| Variable_name | Value |

+------------------------------------------------+--------------+

| bind_address | 10.20.15.11 |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_encryption | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_expire_logs_auto_purge | ON |

| binlog_expire_logs_seconds | 0 |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_rotate_encryption_master_key_at_startup | OFF |

| binlog_row_event_max_size | 8192 |

| binlog_row_image | FULL |

| binlog_row_metadata | MINIMAL |

| binlog_row_value_options | |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlog_transaction_compression | OFF |

| binlog_transaction_compression_level_zstd | 3 |

| binlog_transaction_dependency_history_size | 25000 |

| binlog_transaction_dependency_tracking | COMMIT_ORDER |

+------------------------------------------------+--------------+

25 rows in set (0.00 sec)

mysql>

mysql> show variables like 'dir%';

Empty set (0.00 sec)

mysql> show variables like '%dir%';

+-----------------------------------------+----------------------------+

| Variable_name | Value |

+-----------------------------------------+----------------------------+

| basedir | /usr/ |

| binlog_direct_non_transactional_updates | OFF |

| character_sets_dir | /usr/share/mysql/charsets/ |

| datadir | /var/lib/mysql/ |

| innodb_data_home_dir | |

| innodb_directories | |

| innodb_doublewrite_dir | |

| innodb_log_group_home_dir | ./ |

| innodb_max_dirty_pages_pct | 90.000000 |

| innodb_max_dirty_pages_pct_lwm | 10.000000 |

| innodb_redo_log_archive_dirs | |

| innodb_temp_tablespaces_dir | ./#innodb_temp/ |

| innodb_tmpdir | |

| innodb_undo_directory | ./ |

| lc_messages_dir | /usr/share/mysql/ |

| plugin_dir | /usr/lib/mysql/plugin/ |

| replica_load_tmpdir | /tmp |

| slave_load_tmpdir | /tmp |

| tmpdir | /tmp |

+-----------------------------------------+----------------------------+

19 rows in set (0.00 sec)

mysql>

mysql> show variables like '%log%';

+------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

| activate_all_roles_on_login | OFF |

| audit_log_buffer_size | 1048576 |

| audit_log_exclude_accounts | root@localhost |

| audit_log_exclude_commands | |

| audit_log_exclude_databases | information_schema,mysql,performance_schema,sys |

| audit_log_file | /var/lib/mysql/audit/audit.log |

| audit_log_flush | OFF |

| audit_log_format | OLD |

| audit_log_handler | FILE |

| audit_log_include_accounts | |

| audit_log_include_commands | alter_db,alter_instance,alter_resource_group,alter_server,alter_table,alter_tablespace,alter_user,alter_user_default_role,analyze,change_db,change_repl_filter,change_replication_source,create_db,create_resource_group,create_role,create_server,create_user,delete,drop_db,drop_event,drop_function,drop_index,drop_procedure,drop_resource_group,drop_role,drop_server,drop_spatial_reference_system,drop_table,drop_trigger,drop_user,drop_view,grant,grant_roles |

| audit_log_include_databases | |

| audit_log_policy | ALL |

| audit_log_rotate_on_size | 1073741824 |

| audit_log_rotations | 10 |

| audit_log_strategy | ASYNCHRONOUS |

| audit_log_syslog_facility | LOG_USER |

| audit_log_syslog_ident | percona-audit |

| audit_log_syslog_priority | LOG_INFO |

| back_log | 200 |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_encryption | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_expire_logs_auto_purge | ON |

| binlog_expire_logs_seconds | 0 |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_rotate_encryption_master_key_at_startup | OFF |

| binlog_row_event_max_size | 8192 |

| binlog_row_image | FULL |

| binlog_row_metadata | MINIMAL |

| binlog_row_value_options | |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlog_transaction_compression | OFF |

| binlog_transaction_compression_level_zstd | 3 |

| binlog_transaction_dependency_history_size | 25000 |

| binlog_transaction_dependency_tracking | COMMIT_ORDER |

| expire_logs_days | 31 |

| general_log | OFF |

| general_log_file | /var/lib/mysql/anuragdbm01.log |

| innodb_api_enable_binlog | OFF |

| innodb_flush_log_at_timeout | 1 |

| innodb_flush_log_at_trx_commit | 1 |

| innodb_log_buffer_size | 268435456 |

| innodb_log_checksums | ON |

| innodb_log_compressed_pages | ON |

| innodb_log_file_size | 134217728 |

| innodb_log_files_in_group | 4 |

| innodb_log_group_home_dir | ./ |

| innodb_log_spin_cpu_abs_lwm | 80 |

| innodb_log_spin_cpu_pct_hwm | 50 |

| innodb_log_wait_for_flush_spin_hwm | 400 |

| innodb_log_write_ahead_size | 8192 |

| innodb_log_writer_threads | ON |

| innodb_max_undo_log_size | 1073741824 |

| innodb_online_alter_log_max_size | 134217728 |

| innodb_print_ddl_logs | OFF |

| innodb_redo_log_archive_dirs | |

| innodb_redo_log_capacity | 104857600 |

| innodb_redo_log_encrypt | OFF |

| innodb_undo_log_encrypt | OFF |

| innodb_undo_log_truncate | ON |

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binlog/binlog |

| log_bin_index | /var/lib/mysql/binlog/binlog.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_error | /var/log/mysql/error.log |

| log_error_services | log_filter_internal; log_sink_internal |

| log_error_suppression_list | |

| log_error_verbosity | 2 |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_raw | OFF |

| log_replica_updates | ON |

| log_slave_updates | ON |

| log_slow_admin_statements | OFF |

| log_slow_extra | OFF |

| log_slow_replica_statements | OFF |

| log_slow_slave_statements | OFF |

| log_statements_unsafe_for_binlog | ON |

| log_throttle_queries_not_using_indexes | 0 |

| log_timestamps | UTC |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 104857600 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| max_relay_log_size | 0 |

| relay_log | anuragdbm01-relay-bin |

| relay_log_basename | /var/lib/mysql/anuragdbm01-relay-bin |

| relay_log_index | /var/lib/mysql/anuragdbm01-relay-bin.index |

| relay_log_info_file | relay-log.info |

| relay_log_info_repository | TABLE |

| relay_log_purge | ON |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

| slow_query_log | ON |

| slow_query_log_file | /var/lib/mysql/anuragdbm01-slow.log |

| sql_log_bin | ON |

| sql_log_off | OFF |

| sync_binlog | 1 |

| sync_relay_log | 10000 |

| sync_relay_log_info | 10000 |

| terminology_use_previous | NONE |

110 rows in set (0.00 sec)

mysql> exit

Bye

-- Remove audit log file old from directory

root@anuragdbm01:/etc/mysql/mysql.conf.d# cd /var/lib/mysql

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# ls -lrth

total 91M

-rw-r----- 1 mysql mysql 56 Aug 16 06:04 auto.cnf

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 performance_schema

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 ca.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 ca-key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 server-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 server-cert.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 client-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 client-cert.pem

-rw-r--r-- 1 mysql mysql 452 Aug 16 06:04 public_key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 private_key.pem

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 mysql

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 sys

-rw-r--r-- 1 mysql mysql 0 Aug 16 06:05 debian-5.7.flag

-rw-r----- 1 mysql mysql 180 Aug 16 06:05 binlog.000001

-rw-r----- 1 mysql mysql 404 Aug 16 06:05 binlog.000002

-rw-r----- 1 mysql mysql 48 Aug 16 06:05 binlog.index

-rw-r----- 1 mysql mysql 2.4K Aug 16 06:41 binlog.000003

-rw-r----- 1 mysql mysql 3.5K Aug 16 06:41 ib_buffer_pool

-rw-r----- 1 mysql mysql 1.6K Aug 16 06:41 audit.log

-rw-r----- 1 mysql mysql 8.6M Aug 16 06:43 '#ib_16384_1.dblwr'

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:43 '#innodb_temp'

drwxr-xr-x 2 mysql mysql 4.0K Aug 16 06:43 audit

-rw-r----- 1 mysql mysql 178 Aug 16 06:43 anuragdbm01-slow.log

drwxr-xr-x 2 mysql mysql 4.0K Aug 16 06:43 binlog

-rw-r----- 1 mysql mysql 5 Aug 16 06:43 anuragdbm01.pid

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:43 '#innodb_redo'

-rw-r----- 1 mysql mysql 12M Aug 16 06:43 ibdata1

-rw-r----- 1 mysql mysql 12M Aug 16 06:43 ibtmp1

-rw-r----- 1 mysql mysql 25M Aug 16 06:43 mysql.ibd

-rw-r----- 1 mysql mysql 16M Aug 16 06:45 undo_002

-rw-r----- 1 mysql mysql 16M Aug 16 06:45 undo_001

-rw-r----- 1 mysql mysql 576K Aug 16 06:45 '#ib_16384_0.dblwr'

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# rm -rf audit.log

-- Also Remove Bin Log files old

root@anuragdbm01:/var/lib/mysql# ls -lrth binlog .*

..:

total 204K

drwxr-xr-x 2 root root 4.0K Jun 21 2021 libnss-ldap

drwxr-xr-x 2 root root 4.0K Jan 14 2022 unattended-upgrades

drwxr-xr-x 2 tss tss 4.0K Jan 25 2022 tpm

drwxr-xr-x 2 root root 4.0K Feb 7 2022 boltd

drwxr-xr-x 2 root root 4.0K Mar 9 2022 upower

drwxr-xr-x 2 root root 4.0K Mar 16 2022 ubuntu-drivers-common

drwxr-xr-x 2 root root 4.0K Mar 18 2022 plymouth

drwxr-xr-x 2 root root 4.0K Mar 24 2022 os-prober

drwxr-xr-x 2 root root 4.0K Mar 25 2022 usb_modeswitch

drwxr-xr-x 2 root root 4.0K Apr 18 2022 misc

drwxr-xr-x 2 root root 4.0K Jun 21 2022 dhcp

drwxr-xr-x 2 root root 4.0K Jul 5 2022 git

-rw-r--r-- 1 root root 0 Aug 9 2022 shells.state

drwxr-xr-x 2 root root 4.0K Aug 9 2022 python

drwx------ 3 root root 4.0K Aug 9 2022 polkit-1

drwx------ 2 root root 4.0K Aug 9 2022 private

drwxr-xr-x 3 root root 4.0K Aug 9 2022 sudo

drwxr-xr-x 3 root root 4.0K Aug 9 2022 apport

drwxr-xr-x 3 root root 4.0K Aug 9 2022 vim

drwxr-xr-x 2 root root 4.0K Aug 9 2022 usbutils

drwxr-xr-x 2 root root 4.0K Aug 9 2022 man-db

drwxr-xr-x 4 root root 4.0K Jan 30 2024 grub

drwxr-xr-x 2 root root 4.0K Jan 30 2024 resolvconf

drwxr-xr-x 2 root root 4.0K Jan 30 2024 dbus

drwx------ 2 root root 4.0K Jan 30 2024 udisks2

drwxr-xr-x 10 root root 4.0K Jan 30 2024 systemd

drwxr-xr-x 2 landscape landscape 4.0K Jan 30 2024 landscape

drwxr-xr-x 2 root root 4.0K Jan 30 2024 PackageKit

drwxr-xr-x 2 root root 4.0K Jul 24 11:08 mysql-upgrade

drwxr-xr-x 8 root root 4.0K Jul 26 07:49 cloud

drwxr-xr-x 6 root root 4.0K Jul 26 13:54 fwupd

drwxr-xr-x 2 root root 4.0K Aug 1 10:20 update-manager

drwxr-xr-x 3 root root 4.0K Aug 5 10:15 ucf

drwxr-xr-x 2 root root 4.0K Aug 5 10:40 ubuntu-release-upgrader

drwxr-xr-x 5 root root 4.0K Aug 5 18:53 ubuntu-advantage

drwxr-xr-x 2 root root 4.0K Aug 6 13:29 pam

drwxr-x--- 2 _chrony _chrony 4.0K Aug 7 06:41 chrony

drwxr-xr-x 2 root root 4.0K Aug 7 06:41 sntp

drwxr-xr-x 2 root root 4.0K Aug 16 05:00 logrotate

drwxr-xr-x 2 root root 4.0K Aug 16 05:57 command-not-found

drwxr-xr-x 2 ntp ntp 4.0K Aug 16 06:00 ntp

drwxr-xr-x 3 root root 4.0K Aug 16 06:01 kdump

drwxr-xr-x 5 root root 4.0K Aug 16 06:04 apt

drwxr-xr-x 3 root root 4.0K Aug 16 06:04 mecab

drwx------ 2 mysql mysql 4.0K Aug 16 06:04 mysql-keyring

drwx------ 2 mysql mysql 4.0K Aug 16 06:05 mysql-files

drwxr-xr-x 7 root root 4.0K Aug 16 06:05 dpkg

drwxr-xr-x 4 root root 4.0K Aug 16 06:05 update-notifier

drwxr-xr-x 2 clamav clamav 4.0K Aug 16 06:08 clamav

drwxr-xr-x 23 root root 4.0K Aug 16 06:46 snapd

drwx------ 2 root root 4.0K Aug 16 06:48 NetworkManager

drwx------ 9 mysql mysql 4.0K Aug 16 06:48 mysql

binlog:

total 8.0K

-rw-r----- 1 mysql mysql 36 Aug 16 06:43 binlog.index

-rw-r----- 1 mysql mysql 157 Aug 16 06:43 binlog.000001

total 91M

-rw-r----- 1 mysql mysql 56 Aug 16 06:04 auto.cnf

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 performance_schema

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 ca.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 ca-key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 server-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 server-cert.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 client-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 client-cert.pem

-rw-r--r-- 1 mysql mysql 452 Aug 16 06:04 public_key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 private_key.pem

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 mysql

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 sys

-rw-r--r-- 1 mysql mysql 0 Aug 16 06:05 debian-5.7.flag

-rw-r----- 1 mysql mysql 180 Aug 16 06:05 binlog.000001

-rw-r----- 1 mysql mysql 404 Aug 16 06:05 binlog.000002

-rw-r----- 1 mysql mysql 48 Aug 16 06:05 binlog.index

-rw-r----- 1 mysql mysql 2.4K Aug 16 06:41 binlog.000003

-rw-r----- 1 mysql mysql 3.5K Aug 16 06:41 ib_buffer_pool

-rw-r----- 1 mysql mysql 8.6M Aug 16 06:43 '#ib_16384_1.dblwr'

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:43 '#innodb_temp'

drwxr-xr-x 2 mysql mysql 4.0K Aug 16 06:43 audit

-rw-r----- 1 mysql mysql 178 Aug 16 06:43 anuragdbm01-slow.log

drwxr-xr-x 2 mysql mysql 4.0K Aug 16 06:43 binlog

-rw-r----- 1 mysql mysql 5 Aug 16 06:43 anuragdbm01.pid

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:43 '#innodb_redo'

-rw-r----- 1 mysql mysql 12M Aug 16 06:43 ibdata1

-rw-r----- 1 mysql mysql 12M Aug 16 06:43 ibtmp1

-rw-r----- 1 mysql mysql 25M Aug 16 06:43 mysql.ibd

-rw-r----- 1 mysql mysql 16M Aug 16 06:45 undo_002

-rw-r----- 1 mysql mysql 16M Aug 16 06:45 undo_001

-rw-r----- 1 mysql mysql 576K Aug 16 06:45 '#ib_16384_0.dblwr'

root@anuragdbm01:/var/lib/mysql# ls -lrth binlog.*

-rw-r----- 1 mysql mysql 180 Aug 16 06:05 binlog.000001

-rw-r----- 1 mysql mysql 404 Aug 16 06:05 binlog.000002

-rw-r----- 1 mysql mysql 48 Aug 16 06:05 binlog.index

-rw-r----- 1 mysql mysql 2.4K Aug 16 06:41 binlog.000003

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# rm -rf binlog.000001 binlog.000002 binlog.index binlog.000003

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# ls -lrth

total 91M

-rw-r----- 1 mysql mysql 56 Aug 16 06:04 auto.cnf

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 performance_schema

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 ca.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 ca-key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 server-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 server-cert.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 client-key.pem

-rw-r--r-- 1 mysql mysql 1.1K Aug 16 06:04 client-cert.pem

-rw-r--r-- 1 mysql mysql 452 Aug 16 06:04 public_key.pem

-rw------- 1 mysql mysql 1.7K Aug 16 06:04 private_key.pem

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 mysql

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:04 sys

-rw-r--r-- 1 mysql mysql 0 Aug 16 06:05 debian-5.7.flag

-rw-r----- 1 mysql mysql 3.5K Aug 16 06:41 ib_buffer_pool

-rw-r----- 1 mysql mysql 8.6M Aug 16 06:43 '#ib_16384_1.dblwr'

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:43 '#innodb_temp'

drwxr-xr-x 2 mysql mysql 4.0K Aug 16 06:43 audit

-rw-r----- 1 mysql mysql 178 Aug 16 06:43 anuragdbm01-slow.log

drwxr-xr-x 2 mysql mysql 4.0K Aug 16 06:43 binlog

-rw-r----- 1 mysql mysql 5 Aug 16 06:43 anuragdbm01.pid

drwxr-x--- 2 mysql mysql 4.0K Aug 16 06:43 '#innodb_redo'

-rw-r----- 1 mysql mysql 12M Aug 16 06:43 ibdata1

-rw-r----- 1 mysql mysql 12M Aug 16 06:43 ibtmp1

-rw-r----- 1 mysql mysql 25M Aug 16 06:43 mysql.ibd

-rw-r----- 1 mysql mysql 16M Aug 16 06:45 undo_002

-rw-r----- 1 mysql mysql 16M Aug 16 06:45 undo_001

-rw-r----- 1 mysql mysql 576K Aug 16 06:45 '#ib_16384_0.dblwr'

root@anuragdbm01:/var/lib/mysql#

root@anuragdbm01:/var/lib/mysql# systemctl restart mysql.service

-- Check Audit Working or not by wrong password

root@anuragdbm01:/var/lib/mysql# cd /var/lib/mysql/audit/

root@anuragdbm01:/var/lib/mysql/audit#

root@anuragdbm01:/var/lib/mysql/audit# ls -lrth

total 4.0K

-rw-r----- 1 mysql mysql 680 Aug 16 06:50 audit.log

root@anuragdbm01:/var/lib/mysql/audit#

root@anuragdbm01:/var/lib/mysql/audit# less audit.log

root@anuragdbm01:/var/lib/mysql/audit# mysql -uptdbaanurag -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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

Bye

root@anuragdbm01:/var/lib/mysql/audit#

root@anuragdbm01:/var/lib/mysql/audit# less audit.log

root@anuragdbm01:/var/lib/mysql/audit# mysql -uptdbaanurag -p -A

Enter password:

ERROR 1045 (28000): Access denied for user 'ptdbaanurag'@'localhost' (using password: NO)

root@anuragdbm01:/var/lib/mysql/audit# less audit.log

root@anuragdbm01:/var/lib/mysql/audit#

root@anuragdbm01:/var/lib/mysql/audit# df -h

Filesystem Size Used Avail Use% Mounted on

tmpfs 1.6G 1.1M 1.6G 1% /run

/dev/vda1 40G 7.8G 30G 21% /

tmpfs 7.7G 0 7.7G 0% /dev/shm

tmpfs 5.0M 0 5.0M 0% /run/lock

tmpfs 1.6G 4.0K 1.6G 1% /run/user/10009

tmpfs 1.6G 4.0K 1.6G 1% /run/user/10008

root@anuragdbm01:/var/lib/mysql/audit#

-- Create Database

root@anuragdbm01:/var/lib/mysql/audit# mysql -uptdbaanurag -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

----------------------------------------------------

rm -rf binlog.000001 binlog.000002 binlog.index binlog.000003

create database anurag_test_db

mysql -uptdbaanurag -p -A

Y0urP@$$123

create table test (

audit_log_ID bigint not null,

) engine=InnoDB;

source /home/adobade/backup/a.sql

mysql -h servername -u username -p databasename < filename.sql

mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql &> /home/adobade/backup/a.mylog

/home/adobade/backup/a.sql

mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mpi-and-mngr-schema-mysql.sql &> /home/adobade/backup/mpi-and-mngr-schema-mysql.mylog

mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mdmdbUpdateMysql.sql &> /home/adobade/backup/mdmdbUpdateMysql.mylog

-------------------------------------

mysql>

mysql> create database anurag_test_db default charset='utf8';

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> create database anurag_test_db default charset='utf8';

ERROR 1007 (HY000): Can't create database 'anurag_test_db'; database exists

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| anurag_test_db |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql>

mysql> exit

Bye

----------------------------------------------------------------

-----------------------------------------------------------------

--- Restore database scripts

---Testing import & log output

root@anuragdbm01:/home/adobade/backup# vim a

a.sql audit_log.so

root@anuragdbm01:/home/adobade/backup# vim a.sql

root@anuragdbm01:/home/adobade/backup#

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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>

mysql> use anurag_test_db

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> source /home/adobade/backup/a.sql

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+-------------------+

| Tables_in_anurag_test_db |

+-------------------+

| test |

+-------------------+

1 row in set (0.00 sec)

mysql>

mysql> drop table test

-> ;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql>

mysql> exit

Bye

root@anuragdbm01:/home/adobade/backup#

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql

Enter password:

root@anuragdbm01:/home/adobade/backup#

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql

Enter password:

ERROR 1050 (42S01) at line 1: Table 'test' already exists

root@anuragdbm01:/home/adobade/backup#

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql &> /home/adobade/backup/a.mylog

Enter password:

root@anuragdbm01:/home/adobade/backup#

root@anuragdbm01:/home/adobade/backup# less /home/adobade/backup/a.mylog

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 18

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| anurag_test_db |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql>

mysql> use anurag_test_db;

Database changed

mysql>

mysql> show tables;

+-------------------+

| Tables_in_anurag_test_db |

+-------------------+

| test |

+-------------------+

1 row in set (0.00 sec)

--- Check script modify as required and restore script dml & dump on datbase

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mpi-and-mngr-schema-mysql.sql &> /home/adobade/backup/mpi-and-mngr-schema-mysql.mylog

Enter password:

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mdmdbUpdateMysql.sql &> /home/adobade/backup/mdmdbUpdateMysql.mylog

Enter password:

root@anuragdbm01:/home/adobade/backup#

root@anuragdbm01:/home/adobade/backup# mysql -uptdbaanurag -p -A

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21

Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

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> use anurag_test_db;

Database changed

mysql>

mysql> show tables;

+--------------------------+

| Tables_in_anurag_test_db |

+--------------------------+

| mdmauditlog |

| mdmdirectory2 |

| mdmentrypasswords |

| mdmevent2 |

| mdmfactor2 |

| mdmfiles |

| mdmids |

| mdmmerchant |

| mdmmerdsdata |

| mdmmerdsdata2 |

| mdmnotificationscheduler |

| mdmpanmatch |

| mdmsettings |

| mdmtransaction2 |

| mdmuserroles |

| mdmusers |

| mdmversion |

| mdmwwwsession |

| mdv_processor |

+--------------------------+

19 rows in set (0.00 sec)

mysql>

mysql> exit

-- Application Login & Application User Full Rights User for Temporary application testing purpose

mysql> use anurag_test_db;

Database changed

mysql>

mysql> CREATE USER 'anurag_app'@'%' IDENTIFIED BY 'md5rmAp$';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql>

mysql> CREATE USER 'anurag_app'@'%' IDENTIFIED BY 'md5rmAp2';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql>

mysql> CREATE USER 'anurag_app'@'%' IDENTIFIED BY 'md5rmApp$2';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql>

mysql> CREATE USER 'anurag_app'@'%' IDENTIFIED BY 'Md5rmApp@2';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql>

mysql> CREATE USER 'anurag_app'@'10.11.20.11' IDENTIFIED BY 'EM2qTKk@hsFe';

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'anurag_app'@'10.11.20.12' IDENTIFIED BY 'EM2qTKk@hsFe';

Query OK, 0 rows affected (0.00 sec)

--- App can connect only from below ip for security of accessing anonymous servers

mysql> grant select,insert,update,delete,execute on anurag_test_db.* to 'anurag_app'@'10.11.20.11'

-> ;

Query OK, 0 rows affected (0.01 sec)

mysql> grant select,insert,update,delete,execute on anurag_test_db.* to 'anurag_app'@'10.11.20.12';

Query OK, 0 rows affected (0.00 sec)

-- flush privileges to update schema refresh instant work

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> select user,host from mysql.user;

+------------------+------------+

| user | host |

+------------------+------------+

| ptdbaanurag | % |

| anurag_test | % |

| anurag_app | 10.11.20.11 |

| anurag_app | 10.11.20.12 |

| debian-sys-maint | localhost |

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+------------+

9 rows in set (0.00 sec)

mysql>

mysql> show grants for 'anurag_app'@'10.11.20.11';

+--------------------------------------------------------------------------------------------+

| Grants for anurag_app@10.11.20.11 |

+--------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `anurag_app`@`10.11.20.11` |

| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `anurag_test_db`.* TO `anurag_app`@`10.11.20.11` |

+--------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show grants for 'anurag_app'@'10.11.20.12';

+--------------------------------------------------------------------------------------------+

| Grants for anurag_app@10.11.20.12 |

+--------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `anurag_app`@`10.11.20.12` |

| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `anurag_test_db`.* TO `anurag_app`@`10.11.20.12` |

+--------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql>

-- We have provided wrong ip which apps connect, reconfigure and check whether its working from application server by installing mssql client on application server

---------

CREATE USER 'modirm_app'@'10.20.13.11' IDENTIFIED BY 'EM2qTKk@hsFe';

CREATE USER 'modirm_app'@'10.20.13.12' IDENTIFIED BY 'EM2qTKk@hsFe';

grant select,insert,update,delete,execute on anurag_test_db.* to 'anurag_app'@'10.20.13.11';

grant select,insert,update,delete,execute on anurag_test_db.* to 'anurag_app'@'10.20.13.12';

---------

-- Connect app server

mysql -uanurag_app -h 10.20.15.11 -p

EM2qTKk@hsFe

ssh anuragapp01

-- Also created one application user with full rights for testing and will remove after setup

CREATE USER 'sathish'@'%' IDENTIFIED BY 'SA2qTKk@hsSd';

GRANT ALL PRIVILEGES ON *.* TO 'sathish'@'%'; WITH GRANT OPTION;

-----------------------------------------

History of command issued while installing: -

adobade@anuragdbm01:~$

adobade@anuragdbm01:~$ history

1 2024-08-16 05:46:24 adobade sudo adobade

2 2024-08-16 05:57:47 adobade sudo apt update

3 2024-08-16 05:58:48 adobade clear

4 2024-08-16 05:58:55 adobade sudo apt-get update

5 2024-08-16 05:59:09 adobade sudo apt-get upgrade

6 2024-08-16 06:01:07 adobade sudo apt-get dist-upgrade

7 2024-08-16 06:02:31 adobade sudo reboot

8 2024-08-16 06:04:45 adobade sudo apt install mysql-server

9 2024-08-16 06:05:26 adobade sudo systemctl status mysql.service

10 2024-08-16 06:05:57 adobade sudo su root

11 2024-08-16 08:30:20 adobade history

adobade@anuragdbm01:~$

--------------

92 mysql

93 mysql -U root -P

94 mysql -uroot -p -A

95 sudo mysql_secure_installation

96 mysql -U root -P -A

97 mysql -uroot -p -A

98 ls -l

99 cd backup/

100 ls -l

101 less audit_log.so

102 mysql -uroot -p -A

103 cp /home/adobade/backup/audit_log.so /usr/lib/mysql/plugin/

104 cd /usr/lib/mysql/plugin/

105 ls -lrth

106 mysql -uroot -p -A

107 cd /var/lib/mysql/

108 ls -lrth

109 mkdir audit

110 chown -R mysql:mysql /var/lib/mysql

111 mkdir binlog

112 cd /etc/mysql/mysql.conf.d

113 ls -lrth

114 mv mysqld.cnf mysqld.cnf_16Aug2024

115 ls -lrth

116 vi mysqld.cnf

117 systemctl restart mysql.service

118 systemctl status mysql

119 chown -R mysql:mysql /var/lib/mysql

120 systemctl restart mysql.service

121 systemctl status mysql.service

122 mysql -uroot -p -A

123 cd /var/lib/mysql

124 ls -lrth

125 rm -rf audit.log

126 ls -lrth binlog .*

127 ls -lrth binlog.*

128 rm -rf binlog.000001 binlog.000002 binlog.index binlog.000003

129 ls -lrth

130 systemctl restart mysql.service

131 cd /var/lib/mysql/audit/

132 ls -lrth

133 less audit.log

134 mysql -uptdbaanurag -p -A

135 less audit.log

136 mysql -uptdbaanurag -p -A

137 less audit.log

138 df -h

139 mysql

140* mysql

141 mysql -uptdbaanurag -p -A

142 mysql -uptdbaanurag -p -A

143 cd /home/anurag

144 cd /home/adobade

145 ls -l

146 cd backup/

147 vim a.sql

148 less a.sql

149 mysql -uptdbaanurag -p -A

150 vim a.sql

151 mysql -uptdbaanurag -p -A

152 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql

153 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql -o /home/adobade/backup/a.log

154 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/a.sql &> /home/adobade/backup/a.mylog

155 less /home/adobade/backup/a.mylog

156 mysql -uptdbaanurag -p -A

157 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mpi-and-mngr-schema-mysql.sql &> /home/adobade/backup/mpi-and-mngr-schema-mysql.mylog

158 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mdmdbUpdateMysql.sql &> /home/adobade/backup/mdmdbUpdateMysql.mylog

159 mysql -uptdbaanurag -p -A

160 ls -lrth

161 less mpi-and-mngr-schema-mysql.sql

162 less mpi-and-mngr-schema-mysql.mylog

163 less mdmdbUpdateMysql.mylog

164 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mpi-and-mngr-schema-mysql.sql &> /home/adobade/backup/mpi-and-mngr-schema-mysql.mylog

165 less /home/adobade/backup/mpi-and-mngr-schema-mysql.mylog

166 mysql -uptdbaanurag -p anurag_test_db < /home/adobade/backup/mdmdbUpdateMysql.sql &> /home/adobade/backup/mdmdbUpdateMysql.mylog

167 less /home/adobade/backup/mdmdbUpdateMysql.mylog

168 mysql -uptdbaanurag -p -A

169 history

root@anuragdbm01:/home/adobade/backup#

---------------

--Configuration file which we modified both bind address, to app address and then changed to 0.0.0.0 both as issue while connecting

# The MySQL database server configuration file.

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

[mysqld]

# * Basic Settings

user = mysql

# pid-file = /var/run/mysqld/mysqld.pid

# socket = /var/run/mysqld/mysqld.sock

# port = 3306

# datadir = /var/lib/mysql

#innodb

innodb_log_files_in_group = 4

innodb_buffer_pool_instances = 8

innodb_buffer_pool_size = 6G

innodb_log_buffer_size = 256M

innodb_thread_concurrency = 4

innodb_file_per_table = ON

innodb_stats_on_metadata = OFF

innodb_log_file_size = 128M

#innodb_tmpdir = /sqldata/temp

#connection

max_connections = 200

#datadir

#datadir = /var/lib/mysql

#binlog

log_bin = /var/lib/mysql/binlog/binlog

expire_logs_days = 31

#slow query log

slow_query_log = 1

long_query_time= 10

#Audit

audit_log_file = /var/lib/mysql/audit/audit.log

audit_log_exclude_accounts = root@localhost

audit_log_include_commands = alter_db,alter_instance,alter_resource_group,alter_server,alter_table,alter_tablespace,alter_user,alter_user_default_role,analyze,change_db,change_repl_filter,change_replication_source,create_db,create_resource_group,create_role,create_server,create_user,delete,drop_db,drop_event,drop_function,drop_index,drop_procedure,drop_resource_group,drop_role,drop_server,drop_spatial_reference_system,drop_table,drop_trigger,drop_user,drop_view,grant,grant_roles

audit_log_policy = ALL

audit_log_rotate_on_size=1024M

audit_log_rotations=10

audit_log_exclude_databases=information_schema,mysql,performance_schema,sys

#Password_policy

validate_password.policy=MEDIUM

validate_password.length=12

validate_password.mixed_case_count=1

validate_password.number_count=1

validate_password.special_char_count=1

# If MySQL is running as a replication slave, this should be

# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir

# tmpdir = /tmp

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address = 10.171.0.4

mysqlx-bind-address = 10.171.0.4

skip-name-resolve

# * Fine Tuning

key_buffer_size = 16M

# max_allowed_packet = 64M

# thread_stack = 256K

# thread_cache_size = -1

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover-options = BACKUP

# max_connections = 151

# table_open_cache = 4000

# * Logging and Replication

# Both location gets rotated by the cronjob.

# Log all queries

# Be aware that this log type is a performance killer.

# general_log_file = /var/log/mysql/query.log

# general_log = 1

# Error log - should be very few entries.

log_error = /var/log/mysql/error.log

# Here you can see queries with especially long duration

# slow_query_log = 1

# slow_query_log_file = /var/log/mysql/mysql-slow.log

# long_query_time = 2

# log-queries-not-using-indexes

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

# server-id = 1

# log_bin = /var/log/mysql/mysql-bin.log

# binlog_expire_logs_seconds = 2592000

max_binlog_size = 100M

# binlog_do_db = include_database_name

# binlog_ignore_db = include_database_name

DBA

MySQL Master/Slave Replication Configuration Commands

Post a Comment

TSQL: Constraints

Steps before uninstalling SQL Service Pack

Steps before uninstalling SQL Server

SSIS: SSIS Package scheduling via agent job

Steps for Service Pack (SP) Installation on RTM SQL Server