Steps to Reclaim Disk Space from ibdata1

 

1. Dump All Databases

First, create a backup of all databases to ensure you don't lose any data.

bash
mysqldump -u root -p --all-databases > all_databases.sql

2. Stop MySQL

Stop the MySQL service to prepare for removing the InnoDB data files.

bash
sudo systemctl stop mysqld

3. Remove InnoDB Data Files

Remove the InnoDB data files. These files are typically located in the /var/lib/mysql directory. Be very careful with this step, as removing the wrong files can result in data loss.

bash
sudo rm -rf /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1

4. Edit MySQL Configuration

Ensure innodb_file_per_table is enabled in the MySQL configuration file (usually located at /etc/my.cnf or /etc/mysql/my.cnf).

Add or ensure the following lines are present under the [mysqld] section:

ini
[mysqld]
innodb_file_per_table=1

This setting makes sure that each InnoDB table and its indexes are stored in a separate file, which makes it easier to manage disk space in the future.

5. Start MySQL

Start the MySQL service again.

bash
sudo systemctl start mysqld

6. Restore the Dumped Databases

Import the SQL dump file you created earlier.

bash
mysql -u root -p < all_databases.sql

Additional Tips

• Optimize Tables Regularly: Regularly running OPTIMIZE TABLE can help keep table sizes down and manage free space better.

  sql
  OPTIMIZE TABLE your_table;
  

• Monitor Disk Usage: Use monitoring tools and set up alerts for disk space usage to avoid running into space issues in the future.

• Log Rotation: Ensure logs are being rotated properly to avoid log files consuming too much space.

  bash
  sudo logrotate /etc/logrotate.conf
  

By following these steps, you should be able to reclaim disk space used by the ibdata1 file. This process involves some downtime, so plan accordingly.

create user with remote access

To create a MySQL user called "admin" with the password "asterisk" and grant all privileges to the "asterisk" database, as well as allow remote MySQL connections, follow these steps:

1. Log in to the MySQL server as the root user:

   css
   mysql -u root -p
   

2. Enter the MySQL root password when prompted.

3. Create a new user "admin" with the password "asterisk":

   sql
   CREATE USER 'admin'@'%' IDENTIFIED BY 'asterisk';
   

   This creates a user named "admin" that can connect from any remote host ("%").

4. Grant all privileges to the user on the "asterisk" database:

   sql
   GRANT ALL PRIVILEGES ON asterisk.* TO 'admin'@'%';
   

5. Flush the privileges to apply the changes:

   sql
   FLUSH PRIVILEGES;

Allow remote connection

nano my.cnf.d/mariadb-server.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mariadb/mariadb.log

pid-file=/run/mariadb/mariadb.pid

bind-address=0.0.0.0

Restart the service

 systemctl restart mysqld

TEST

 mysql -h 145.27.107.195 -u admin -pasterisk

How to Allow Remote Connections to MySQL

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

1.2 Change Bind-Address IP

You now have access to the MySQL server configuration file. Scroll down to the bind-address line and change the IP address. The current default IP is set to 127.0.0.1. This IP limits MySQL connections to the local machine.

The new IP should match the address of the machine that needs to access the MySQL server remotely. For example, if you bind MySQL to 0.0.0.0, then any machine that reaches the MySQL server can also connect with it.

Once you make the necessary changes, save and exit the configuration file.

Note: Remote access is additionally verified by using the correct credentials and user parameters you have defined for your MySQL users.

1.3 Restart MySQL Service

Apply the changes made to the MySQL config file by restarting the MySQL service:

sudo systemctl restart mysql

Next, your current firewall settings need to be adjusted to allow traffic to the default MySQL port.

Step 2: Set up Firewall to Allow Remote MySQL Connection

While editing the configuration file, you probably observed that the default MySQL port is 3306. 

https://phoenixnap.com/kb/mysql-remote-connection

MySQL Error-The Server Requested An Authentication Method Unknown To The Client

1 - CREATE USER 'admin'@'%' IDENTIFIED BY '91224YB11111';

2 - GRANT ALL ON *.* TO 'admin'@'%';

3- ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY '91224YB11111';

3 - FLUSH PRIVILEGES;

https://www.skynats.com/blog/mysql-error-the-server-requested-authentication-method-unknown-to-the-client/

use this for create an user on MYSQL 8

 CREATE DATABASE IF NOT EXISTS android

CREATE USER 'user'@'localhost' IDENTIFIED BY 'P@ssW0rd';

GRANT ALL ON *.* TO 'user'@'localhost';

FLUSH PRIVILEGES;

Order a MySQL table by two columns

 

Default sorting is ascending, you need to add the keyword DESC to both your orders:

ORDER BY article_rating DESC, article_time DESC

https://stackoverflow.com/questions/514943/order-a-mysql-table-by-two-columns

$query = " select * from  users where price like '%$_GET[par]%' or  info like '%$_GET[par]%' or keyword  like '%$_GET[par]%' order by price,  id desc";




MYSQL display duplicated values

SELECT      list_phone,list_name,COUNT(list_phone) FROM     lists where list_name='4thbatch-phoneonly' GROUP BY list_phone HAVING COUNT(list_phone) > 1;

| 9896652563 | 4thbatch-phoneonly |                 3 |

| 9896895556 | 4thbatch-phoneonly |                 3 |

| 9897435168 | 4thbatch-phoneonly |                 3 |

| 9897529384 | 4thbatch-phoneonly |                 2 |

| 9898399391 | 4thbatch-phoneonly |                 4 |

| 9898457427 | 4thbatch-phoneonly |                 2 |

| 9898766241 | 4thbatch-phoneonly |                 2 |

| 9898927972 | 4thbatch-phoneonly |                 3 |

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

1943 rows in set (0.10 sec)

https://www.mysqltutorial.org/mysql-find-duplicate-values/