Recovering Data with Binary Logs

When you need to restore data from MySQL binary logs, use the mysqlbinlog utility with specific date-time ranges:

mysqlbinlog --database=sfxt --start-datetime="2021-08-07 12:00:00" --stop-datetime="2022-09-19 18:31:00" /www/server/data/mysql-bin.000022 > /opt/redata3.sql

If encoding issues occur when importing the recovered data, use a text editor like Sublime Text to replace problematic characters before importing.

Database Export Commands

Exporting an entire database to a SQL file:

mysqldump -u username -p database_name > backup_file.sql
mysqldump -u dbuser -p dbname > dbname.sql

Exporting a specific table:

mysqldump -u username -p database_name table_name > table_backup.sql
mysqldump -u dbuser -p dbname users > dbname_users.sql

Exporting database structure only (no data):

mysqldump -u dbuser -p -d --add-drop-table dbname > d:/dbname_db.sql

Options explained: -d means no data, --add-drop-table adds DROP TABLE statements before each CREATE statement.

Exporting from a remote server:

mysqldump -u dbuser -h 39.98.188.62 -p dbname > 2019-11-01.sql

Database Import Commands

Using the MySQL console to import:

mysql -u root -p
mysql> use database_name;
mysql> source d:/dbname.sql;

Importing directly via command line:

mysql -u root -D database_name < backup_file.sql

Importing to a specific table:

mysql -u root -D database_name table_name < table_backup.sql

Table Structure Modifications

Adding a new column to an existing table:

-- Basic column addition
ALTER TABLE kq_sc343_class ADD COLUMN vice_headtea_uid VARCHAR(255);

-- Adding column with default value and comment
ALTER TABLE kq_sc343_class ADD COLUMN vice_headtea_uid VARCHAR(255) NOT NULL DEFAULT '0' COMMENT 'Vice Class Advisor';

Dropping a column:

ALTER TABLE kq_sc343_class DROP COLUMN vice_headtea_uid;

Query Examples: Searching Multiple Fields

Finding records matching a user ID across multiple columns using OR logic:

$where['headteauid'] = array('like', "%{$userid}%");
$where['vice_headtea_uid'] = array('like', "%{$userid}%");
$where['Id'] = array('in', $techclids);
$where['_logic'] = 'or';
$map['_complex'] = $where;
$map['isgrad'] = 0;
$cldata = M($sccode.'_class')->field('Id,name,gradeid')->where($map)-select();

Alternative approach using vertical bar notasion:

// Query teacher-assigned classes (including main and vice homeroom teachers)
$where_like['headteauid|vice_headtea_uid'] = array('like', "%{$userid}%");
$techdata = M($sccode.'_teach')->field('classid')->where(array('uid'=>$userid))->select();
$techclids = $this->getdatatoarray($techdata, 'classid');
if(!$techclids) $techclids = '';
$cldata = M($sccode.'_class')->field('Id,name,gradeid')->where(array(
    array('Id'=>array('in', $techclids), $where_like, '_logic'=>'or'),
    'isgrad'=>0,
    '_logic'=>'and'
))->select();

Using FIND_IN_SET for comma-separated values:

SELECT * FROM sp_sc343_class WHERE FIND_IN_SET('5881', vice_headtea_uid)

Dynamic WHERE clause builder for multiple IDs:

$whereclass = '';
if(is_array($classids)) {
    foreach($classids as $ck=>$cv) {
        $whereclass .= ' FIND_IN_SET('.$cv.', vice_headtea_uid) OR';
    }
    $whereclass = rtrim($whereclass, 'OR');
} else {
    $whereclass = ' FIND_IN_SET('.$classids.', vice_headtea_uid)';
}

Session Management with REPLACE

$sql = "REPLACE INTO `session` VALUES ('$sess_id', '$sess_content', unix_timestamp())";
// Alternative: INSERT ... ON DUPLICATE KEY UPDATE
$sql = "INSERT INTO `session` VALUES ('$sess_id', '$sess_content') ON DUPLICATE KEY UPDATE session_content='$sess_content', last_time=unix_timestamp()";

User Management and Remote Access

Viewing existing MySQL users:

mysql> SELECT host, user, password FROM mysql.user;

Creating a new user:

CREATE USER test IDENTIFIED BY '123456';

Granting privileges for remote access:

GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Modifying user password:

UPDATE mysql.user SET password = PASSWORD('new_password') WHERE User = 'test' AND Host = 'localhost';
FLUSH PRIVILEGES;

Deleting a user:

DELETE FROM mysql.user WHERE User = 'test' AND Host = 'localhost';

Changing root password locally:

SET PASSWORD FOR root@localhost = PASSWORD('Abc_123!');

Backup and Restore Syntax

Backup command structure:

mysqldump -h server_address -u username -p database_name > output_file

Restore to another database:

mysql -h server_address -u username -P port_number -p database_name < backup_file

Note: The target database must exist before running the restore command.