unzip sql into mysql

gunzip -cv drupal10_from_9_20240914.sql.zip | mysql -v -u chiao -p drupal10_from_9

Create class databases and users for dam

Create the following sql file, say, createdam.sql, then run
mysql -u chiao -o mysql < createdam.sql

      --  createdam.sql, ------------------------------------------------------

DROP PROCEDURE IF EXISTS createUserDatabases;
DELIMITER //

CREATE PROCEDURE createUserDatabases()
BEGIN
  -- Create the users and their databases, and grant privileges
  SET @users = 'Team112, Monkey, WhileTrue, clear, DIEGO';
  SET @passwords = 'AA090078, AA090107, AA091227, AA091233, AA091223';
  SET @privileges = 'SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER';

  SET @delimiter = ', ';
  SET @count = 1;
  SET @total_users = LENGTH(@users) - LENGTH(REPLACE(@users, @delimiter, '')) + 1;

  WHILE @count <= @total_users DO
    SET @current_user = SUBSTRING_INDEX(SUBSTRING_INDEX(@users, @delimiter, @count), @delimiter, -1);
    SET @current_password = SUBSTRING_INDEX(SUBSTRING_INDEX(@passwords, @delimiter, @count), @delimiter, -1);
    SET @current_database = CONCAT('dam1112_', @current_user);
    SET @current_privileges = SUBSTRING_INDEX(SUBSTRING_INDEX(@privileges, @delimiter, @count), @delimiter, -1);

    -- Create the user and set the password
    SET @create_user_sql = CONCAT('CREATE USER IF NOT EXISTS \'', @current_user, '\' IDENTIFIED BY \'', @current_password, '\'');
    PREPARE createUserStmt FROM @create_user_sql;
    EXECUTE createUserStmt;
    DEALLOCATE PREPARE createUserStmt;

    -- Create the database and grant privileges
    SET @create_db_sql = CONCAT('CREATE DATABASE IF NOT EXISTS \`', @current_database, '\`');
    PREPARE createDbStmt FROM @create_db_sql;
    EXECUTE createDbStmt;
    DEALLOCATE PREPARE createDbStmt;

    SET @grant_privileges_sql = CONCAT('GRANT ', @current_privileges, ' ON \`', @current_database, '\`.* TO \'', @current_user, '\'');
    PREPARE grantPrivilegesStmt FROM @grant_privileges_sql;
    EXECUTE grantPrivilegesStmt;
    DEALLOCATE PREPARE grantPrivilegesStmt;

    SET @delete_fuzzyahp_sql = CONCAT('DELETE FROM \`fuzzyahp\`.\`fahpdatabase\` WHERE \`fahpdatabase\`.\`user\` = \'',  @current_user, '\' AND \`fahpdatabase\`.\`database\` = \'', @current_database, '\'');
    PREPARE deleteStmt FROM @delete_fuzzyahp_sql;
    EXECUTE deleteStmt;
    DEALLOCATE PREPARE deleteStmt;

    SET @insert_fuzzyahp_sql = CONCAT('INSERT INTO \`fuzzyahp\`.\`fahpdatabase\` (\`user\`, \`database\`) VALUES (\'', @current_user, '\', \'', @current_database, '\')');
    PREPARE insertStmt FROM @insert_fuzzyahp_sql;
    EXECUTE insertStmt;
    DEALLOCATE PREPARE insertStmt;

    SET @count = @count + 1;
  END WHILE;
END //

DELIMITER ;

CALL createUserDatabases();

--  createdam.sql, ------------------------------------------------------

Cannpt login as mysql user root from normal user account

 cf: https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

$ sudo mysql -u root

mysql> DROP USER 'root'@'localhost';

mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '<root_password>';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; 

mysql> FLUSH PRIVILEGES;

In above, One replace 'root' e.g. with 'chiao' to create the a mysql user as root.  

setup permission for mysqldump

1. Create ~/.my.cnf, add these lines:
   

   [mysqldump]
   user=student
   password=student
   chmod 0600 ~/.my.cnf

2. Inside phpmyadmin, set 'Select' and 'LOCK TABLES' permission to database 'drupal' for 'student' as the following image:
   
   
   
3. Then, dump drupal with the command without username and password:
   

    mysqldump drupal > kpchiao9600k_drupal.sql

   or
   

    mysqldump -B drupal > kpchiao9600k_drupal.sql

   The -B option will add the create database and use datatebase lines into dump file as:
   

   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `drupal` /*!40100 DEFAULT CHARACTER SET latin1 */;
   
   USE `drupal`;

   
   
   
   
   

mysql installation on ubuntu 18.04

sudo apt update

sudo apt install mysql-server

sudo mysql_secure_installation

sudo mysql, then issue the command: 
SELECT user,authentication_string,plugin,host FROM mysql.user;


Originally, the root user is authenticated with the "auth_socket" plugin.

To configure the root account to authenticate with a password,

run the following ALTER USER command.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
 
Replacing the 'password' with a new strong password. 
Note that this command will change the root password you set in Step 2. 

clone grade database

Clone from  kpci7:
mysqldump -u chiao -h localhost  -p grade_108_1 > grade_108_1_20200103.sql

to kpchiaoi5:

mysql -u chiao -p grade_108_1 < grade_108_1_20200103.sql