MySQL (Windows) Command Line Operation

If a data volume is generated, You can first import the command line into cmd, in the "switch to MySQL \ bin" directory, run the following command to export all data records in a data volume.

IP address-U of the mysqldump-H server uses anonymous name-P password data register Name> data register name login. SQL

For example: mysqldump-hptw-midts-01-uroots-p1234 packing_netoffice> packing_netoffice. SQL

Command for logging on to MySQL data migration: mysql-H server name-u secret name-P secret

Mysql-hlocalhost-uroot-p1234

Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 3
Server version: 5.0.45-Community-NT-log MySQL Community edition (GPL)

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> -- use packing_netoffice data volume
Mysql> Use packing_netoffice;
Database changed

-- Query the number of rows in the v_v_mid_user_all table
Mysql> select count (1) From v_mid_user_all;
+ ---------- +
| Count (1) |
+ ---------- +
| 1, 512771 |
+ ---------- +
1 row in SET (0.00 Sec)

Mysql> -- collect new packing_netoffice data
Mysql> source c: \ appserv \ mysql \ bin \ packing_netoffice. SQL

Mysql> -- except procedure

Mysql> drop procedure syncaccount;
Query OK, 0 rows affected (0.20 Sec)

The following is the procedure generation of MySQL built by the command line. delimiter // indicates that the entire generation ends.

Delimiter //
Create procedure syncaccount (in deptstr varchar (4000 ))
Begin
Begin
Declare depttotal int default 0;
Declare splitresult varchar (255) default '';
Declare I int default 0;
Declare deptcount int;
Set depttotal = 1 + (length (deptstr)-length (replace (deptstr ,',','')));
Drop table if exists dept_split;

Create temporary table dept_split (dept_id varchar (36) not null) default charset = utf8;
While I <depttotal do
Set I = I + 1;
Set splitresult = reverse (substring_index (deptstr, ',', I), ',', 1 ));
Select count (1) into deptcount from dept_split where dept_id = splitresult;
If deptcount = 0 then
Insert into dept_split (dept_id) values (splitresult );
End if;
End while;
End;
Begin
Declare v_deptname varchar (255 );
Declare v_deptid varchar (36 );
Declare v_isservice char (1) default '1 ';
Declare flag int default 0;
Declare n_count int;
 
Declare v_cursor cursor
Select c_dept_name, dept_id from v_mid_department
Where dept_id
In (select dept_id from dept_split );
Declare continue handler for not found set flag = 1;
Open v_cursor;
While flag <> 1 do
Fetch v_cursor into v_deptname, v_deptid;
 
Select count (1) into n_count from midorganizations where deptid = convert (v_deptid using utf8) Collate utf8_unicode_ci;
If n_count = 0 then
Insert into midorganizations (name, deptid, isservice)
Values (v_deptname, v_deptid, v_isservice );
End if;
End while;
Close v_cursor;
End;
Begin
Declare v_organization mediumint (8 );
Declare v_workid varchar (36 );
Declare v_login varchar (155 );
Declare v_password varchar (155) default '12bz/9hnlplzk ';
Declare v_name varchar (155 );
Declare v_deptid varchar (155 );
Declare v_email_work varchar (155 );
Declare v_phone_work varchar (155 );
Declare v_memberid mediumint (8 );
Declare v_status varchar (1 );
Declare no_data_rows2 int default 0;
Declare n_count2 int;
Declare v_insertmemberid int;
Declare v_profil varchar (1 );
Declare v_created varchar (19) default sysdate ();
Declare v_cursor2 cursor
Select work_id, login_id, native_name, dept_id, office_mail, office_phone, status
From v_mid_user_all
Where dept_id
In (select dept_id from dept_split );
Declare continue handler for not found set no_data_rows2 = 1;
Open v_cursor2;
While no_data_rows2 <> 1 do
Fetch v_cursor2 into v_workid, v_login, v_name, v_deptid, v_email_work, v_phone_work, v_status;
If v_workid = 'la0800534' or v_workid = 'la0801488 'then
Set v_profil = '5 ';
Elseif v_workid = 'la0803279 'or v_workid = 's099000000' or v_workid = 's09902409' or v_workid = 'w10703265' or v_workid = 's10900145' then
Set v_profil = '1 ';
Else
Set v_profil = '8 ';
End if;
If v_login is not null then
Set v_name = Concat (v_login, '(', rtrim (v_name ),')');
End if;
Select count (1) into n_count2 from midmemberid_workid where workid = v_workid;
If n_count2 = 0 then
Select ID into v_organization from midorganizations where deptid = convert (v_deptid using utf8) Collate utf8_unicode_ci;
Insert into midmembers (organization, login, password, name, email_work, phone_work, Profil, created)
Values (v_organization, v_login, v_password, v_name, v_email_work, v_phone_work, v_profil, v_created );
Select max (ID) into v_memberid from midmembers;
Insert into midmemberid_workid (workid, memberid) values (v_workid, v_memberid );
End if;
If v_status = '2' then
Select memberid into v_insertmemberid from midmemberid_workid where workid = v_workid;
Update midmembers set Profil = '9', login = NULL where id = v_insertmemberid;
End if;
End while;
Close v_cursor2;
End;
End ;//