Author: Kiril

Moodle – Mysql query initiate new DB for Moodle

How to quickly create Data Base for Moodle Instance via CLI Mode (Less secure Not for Production Mode)

run: CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
//mysql 5.7
GRANT ALL PRIVILEGES ON moodle.* to 'youredbusername'@'localhost' identified by 'yourepassword'; 
//mysql 8
//Create User
CREATE USER 'moodle'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourepassword';

//grand all permission  
GRANT ALL PRIVILEGES ON moodle.* TO 'moodle'@'localhost';

MOODLE – Backup and restore DB Orders

Export DB DUMP with ignore table mdl_logstore_standard_log

mysqldump moodle --ignore-table=mdl_logstore_standard_log | gzip > /backup/moodledb-date +\%Y-\%m-\%d-\%H-\%M-\%S.sql.gz

Import DB DUMP 

gunzip < moodledb_dump.sql.gz | mysql -u moodle -p moodle

import DB dump without log table 

gzip -cd "./mydb.sql.gz" | sed -r '/INSERT INTO `(log_table_1|log_table_2|log_table_3|log_table_4)`/d' | gzip > "./mydb2.sql.gz"

Moodle – Query to copy logstore_standard_log to logstore_xapi_log

Query to Import all current moodle logs to xapi logs

TRUNCATE mdl_logstore_xapi_log;
INSERT INTO mdl_logstore_xapi_log
 (id, eventname, component, action, target, objecttable, objectid, crud, edulevel, contextid, contextlevel, contextinstanceid, userid, courseid, relateduserid, anonymous, other, timecreated, origin, ip, realuserid) 
 select * 
 from mdl_logstore_standard_log;

Moodle -Query to change all users details

UPDATE mdl_user 
SET 
username=CONCAT('username',mdl_user.id),
phone1=CONCAT('11111',mdl_user.id),
phone2=CONCAT('11111',mdl_user.id),
firstname=CONCAT('Name',mdl_user.id),
lastname=CONCAT('Last',mdl_user.id),
email=CONCAT('email',mdl_user.id,'@test.com'), 
idnumber= CONCAT('1000',mdl_user.id,'2000')
WHERE id>=7