tech . greg milsted . com

If you find the slave you are using is out of sync too much to skip and ignore you really need to do a rebuild.  Sometimes this isn’t possible as the database is a live system and cannot be stopped.

Best to open two windows as the next couple of steps need to be done quickly.

On the master server login to mysql and type; show master status;

Make a note of the file and position (masterserver-bin.00000001,  123321123)
Stop the mysql service on the slave server, service mysqld stop

Now you need to copy the live database (MYI, etc.) files from the master server to the slave:

cd /var/lib/mysql/databasename

scp -pr * 192.168.0.1:/var/lib/mysql/databasename/
(p is to copy permissions and r is to copy recursive just in case)

When the copy finishes restart the service on the slave; service mysqld restart

stop slave;

change master to master_log_file=’servername-bin.0000001′, master_log_pos=101010101010;

Now start the slave (start slave;) and run `show slave status;` you may have to skip a couple of files (depending on your db size and how quick you were in the first couple of steps)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; slave start; show slave status;

Repeat the last step until the database reports no duplicate entries (we also had to repair a table or two - easy `repair table tablename;`)

SELECT *  FROM `role_capabilities` WHERE `roleid` = 7 and capability LIKE ‘%forum%’ ORDER BY `role_capabilities`.`capability` ASC

UPDATE resource SET alltext = replace(alltext,””,”"), name=replace(name,””,”"), summary=replace(summary,””,”")

UPDATE assignment SET description = replace(description,””,”")

UPDATE block_search_documents SET title = replace(title,””,”")

UPDATE cache_text SET formattedtext = replace(formattedtext,””,”")

UPDATE course_sections SET summary = replace(summary,””,”")

UPDATE event SET description = replace(description,””,”")

UPDATE forum SET intro = replace(intro,””,”")

UPDATE forum_posts SET message = replace(message,””,”")

UPDATE glossary_comments SET entrycomment = replace(entrycomment,””,”")

UPDATE glossary_entries SET definition = replace(definition,””,”")

UPDATE label SET name = replace(name,””,”"), content = replace(content,””,”")

UPDATE lesson_pages SET contents = replace(contents,””,”")

UPDATE message SET message = replace(message,””,”")

UPDATE message_read SET message = replace(message,””,”")

UPDATE question SET questiontext = replace(questiontext,””,”"), generalfeedback = replace(generalfeedback,””,”")

UPDATE question_answers SET feedback = replace(feedback,””,”")

UPDATE quiz_feedback SET feedbacktext = replace(feedbacktext,””,”")

TAGS - moodle - strange characters - search and replace

ALTER TABLE tablename ADD newcolumnname VARCHAR(255);

select * into outfile ‘temp.csv’
fields terminated by ‘,’ optionally enclosed by ‘”‘
lines terminated by ‘\n’
from tablename;

File outputs in:

/var/lib/mysql/tablename/temp.csv

mysqladmin create [new database]
mysqldump ——opt [source database] | mysql [new database]

From the MySQL docs:

The easiest (although not the fastest) way to move a database between
two machines is to run the following commands on the machine on which
the database is located:

shell> mysqladmin -h ‘other hostname’ create db_name
shell> mysqldump ——opt db_name \
| mysql -h ‘other hostname’ db_name

Also from the MySQL docs:

–opt Same as –quick –add-drop-table –add-locks –extended-insert
–lock-tables. Should give you the fastest possible dump for reading
into a MySQL server.

Also check out mysqlhotcopy if you want to just backup the MySQL datafiles.

Also:

Export:

mysqldump –add-drop-table -u sadmin -p pass21 Customers > custback.sql

Import

mysql -u username -p -h localhost data-base-name < data.sql

Best way is to simply use yum.

Yum install name of software

Thats it.

mysql -u##### -p####### moodle < base_install_moodle.sql

edit /etc/my.cnf

type log=/var/log/mysql-querylog.log

RESTART mysql