1

I am creating a backup web server but I want the backup server to only have one root user. So after moving all the databases I need to replace all the mysqli_connect(...) lines in db.txt.

I have written:

beg="mysqli_connect("
end=");"
#need to find out the database
db="123"
new="'localhost','root','pass','$db'"

cat db.txt | sed "s/$beg.*$end/$beg$new$end/"

to replace all the configs, but I need to find out the database. I can do this by getting the text between the $end and the previous ',' but how would I go about getting that with sed?


input:

Potential file:

sadnkjnadsjknfaskdjfnlasdnfkdsa
safdjnasdkjjfasndka
asfdknjdsakjfnasjdnkfsadnjkads mysqli_connect("192.168.1.10","dave","password","DavesDB");
sdfnjnweewrewqrfgjdgnf
vncnccnvxjncvjn4wrwerewrewerwjvcnxjnvcj
mysqli_connect("192.168.1.10" , "tom" , "password" , 'TomsDB'); dsanjasdknfjansd
sakfdnjsadjkfnsa
sknfdsjdnfjsdnnqwerweq

output:

sadnkjnadsjknfaskdjfnlasdnfkdsa
safdjnasdkjjfasndka
asfdknjdsakjfnasjdnkfsadnjkads mysqli_connect("localhost","root","rootpassword","DavesDB");
sdfnjnweewrewqrfgjdgnf
vncnccnvxjncvjn4wrwerewrewerwjvcnxjnvcj
mysqli_connect("localhost","root","rootpassword","TomsDB"); dsanjasdknfjansd
sakfdnjsadjkfnsa
sknfdsjdnfjsdnnqwerweq
  • BTW, what you want to do is a bad idea. Create a user that owns and operates this database (and maybe other databases too - but IMO it's best to have dedicated mysql users for unrelated databases). Reserve the root mysql account for administrative tasks, same as the unix root user should be reserved for admin tasks. – cas May 16 '16 at 11:14
  • @cas I definitely agree about 1 DB user per database. But if you want to script out the install for the creation of those user accounts - then that setup script will need full root grant privileges on the server - otherwise how else can you automate the creation of users and their DB pairings? – the_velour_fog May 16 '16 at 11:20
  • @the_velour_fog that's exactly the kind of admin task that mysql root accounts are for. – cas May 16 '16 at 11:23
  • @cas I mean write bash scripts that use the mysql root account to create the user accounts and the DB that they have read write access on - then from that point on - all DB schema restore scripts are run using the DB specific users so they can't destroy other databases on the server. But you still have to perform that initial "dangerous" task using the root account - its unavoidable – the_velour_fog May 16 '16 at 11:31
  • Yes, of course. I never said "never use the root mysql account for anything". I said "reserve it for administrative tasks" (and creating users and databases, and granting access are ALL administrative tasks). BTW, you can use a root mysql account in a script. – cas May 16 '16 at 11:36
1

You don't need to get the database name, there's no point in replacing something with itself. Just leave it unchanged:

beg="mysqli_connect("
new="'localhost','root','pass',"
sed "s/$beg\([^,]\+,\)\{3\}/$beg$new/" file 

If I save the example you gave as file, that returns:

$ sed "s/$beg\([^,]\+,\)\{3\}/$beg$new/" file 
sadnkjnadsjknfaskdjfnlasdnfkdsa
safdjnasdkjjfasndka
asfdknjdsakjfnasjdnkfsadnjkads mysqli_connect('localhost','root','pass',"DavesDB");
sdfnjnweewrewqrfgjdgnf
vncnccnvxjncvjn4wrwerewrewerwjvcnxjnvcj
mysqli_connect('localhost','root','pass', 'TomsDB'); dsanjasdknfjansd
sakfdnjsadjkfnsa
sknfdsjdnfjsdnnqwerweq

The sed regular expression looks for $beg, then three instances of non-, followed by a comma (\([^,]\+,\)\{3\}) and replaces them with $beg$new. The DB name is left as is since there's no reason to change it.

You could also simplify the syntax a little by removing the ( from $beg which lets you use sed's extended regex syntax:

$ sed -E "s/$beg\(([^,]+,){3}/$beg($new/" file 
sadnkjnadsjknfaskdjfnlasdnfkdsa
safdjnasdkjjfasndka
asfdknjdsakjfnasjdnkfsadnjkads mysqli_connect('localhost','root','pass',"DavesDB");
sdfnjnweewrewqrfgjdgnf
vncnccnvxjncvjn4wrwerewrewerwjvcnxjnvcj
mysqli_connect('localhost','root','pass', 'TomsDB'); dsanjasdknfjansd
sakfdnjsadjkfnsa
sknfdsjdnfjsdnnqwerweq

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.